Java sql parameter null

Techie Shah

Java Database Connection (JDBC) API allows the Java programmers to access different relational and NoSQL databases like Oracle, MySQL, SQL Server, etc. It helps to store, access and manipulate the data stored in these databases.

In this post, we will explore how to set the NULL values in PreparedStatement.

Null String data type

For String data type, it does not matter if the value to be set as a parameter in the SQL query is NULL.

Other data types like Integer, Float, Double, Date, etc.

For data types like Integer, Float, Double, Date we have to explicitly call the setNull method.

Example

Solution

Complete Code

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Types; public class JDBCNullExample < public static void main(String[] args) throws Exception< Connection connection = null; try < connection = openConnection(); String name = null; Long PreparedStatement ps = connection.prepareStatement("select * from person where first_name = ? and ps.setString(1,name); if(id == null) < ps.setNull(2, Types.INTEGER); >else < ps.setLong(2, id); >ResultSet rs = ps.executeQuery(); System.out.println(rs.getFetchSize()); rs.close(); >catch(Exception exception) < exception.printStackTrace(); >finally < if(connection != null) < connection.close(); >> > private static Connection openConnection() throws Exception < Class.forName("oracle.jdbc.OracleDriver"); return DriverManager.getConnection("jdbc:oracle:thin:@IP:orcl", "user","password"); >> 

Conclusion

Except for the String data type, we have to explicitly specify the data type if the value is Null for other data types.

• Get link
• Facebook
• Twitter
• Pinterest
• Email
• Other Apps

Labels

• Get link
• Facebook
• Twitter
• Pinterest
• Email
• Other Apps

Comments

This fixes the NPE (which could simply be done by using setObject in any case), but the query will still not work as expected, since NULL needs to be compared with IS instead of «=». See https://stackoverflow.com/q/9996619 for both those aspects. Reply Delete

Post a Comment

Popular posts from this blog

Intellij : How to add @author comment to every new class

Introduction In this tutorial, we will learn how to add @author comments to every new class that we create. We can achieve it using either of the following two solutions Solution 1: Automatically add @author comments to every new class using Files and Code Templates Open File -> Settings -> Editor -> File and Code Templates -> Includes Click on Includes . Under File Header , enter the following comments text /** * @author $ * @Date $ */ Intellij — add @author comments Solution 2: Autocompletion of @author Open File -> Settings -> Editor -> Live Templates Select Java and then click on + button In Abbreviation, enter @a In template text , enter the following comments /** * @author $ * @Date $ */ In option , Expands with select SPACE Intellij — Autocompletion @author You can simply add the @author comments by typing @a and then click SPACE

• Get link
• Facebook
• Twitter
• Pinterest
• Email
• Other Apps

Источник

Spring Data JPA and Null Parameters

The Kubernetes ecosystem is huge and quite complex, so it’s easy to forget about costs when trying out all of the exciting tools.

To avoid overspending on your Kubernetes cluster, definitely have a look at the free K8s cost monitoring tool from the automation platform CAST AI. You can view your costs in real time, allocate them, calculate burn rates for projects, spot anomalies or spikes, and get insightful reports you can share with your team.

Connect your cluster and start monitoring your K8s costs right away:

We rely on other people’s code in our own work. Every day.

It might be the language you’re writing in, the framework you’re building on, or some esoteric piece of software that does one thing so well you never found the need to implement it yourself.

The problem is, of course, when things fall apart in production — debugging the implementation of a 3rd party library you have no intimate knowledge of is, to say the least, tricky.

Lightrun is a new kind of debugger.

It’s one geared specifically towards real-life production environments. Using Lightrun, you can drill down into running applications, including 3rd party dependencies, with real-time logs, snapshots, and metrics.

Learn more in this quick, 5-minute Lightrun tutorial:

Slow MySQL query performance is all too common. Of course it is. A good way to go is, naturally, a dedicated profiler that actually understands the ins and outs of MySQL.

The Jet Profiler was built for MySQL only, so it can do things like real-time query performance, focus on most used tables or most frequent queries, quickly identify performance issues and basically help you optimize your queries.

Critically, it has very minimal impact on your server’s performance, with most of the profiling work done separately — so it needs no server changes, agents or separate services.

Basically, you install the desktop application, connect to your MySQL server, hit the record button, and you’ll have results within minutes:

DbSchema is a super-flexible database designer, which can take you from designing the DB with your team all the way to safely deploying the schema.

The way it does all of that is by using a design model, a database-independent image of the schema, which can be shared in a team using GIT and compared or deployed on to any database.

And, of course, it can be heavily visual, allowing you to interact with the database using diagrams, visually compose queries, explore the data, generate random data, import data or build HTML5 database reports.

The Kubernetes ecosystem is huge and quite complex, so it’s easy to forget about costs when trying out all of the exciting tools.

To avoid overspending on your Kubernetes cluster, definitely have a look at the free K8s cost monitoring tool from the automation platform CAST AI. You can view your costs in real time, allocate them, calculate burn rates for projects, spot anomalies or spikes, and get insightful reports you can share with your team.

Connect your cluster and start monitoring your K8s costs right away:

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

We’re looking for a new Java technical editor to help review new articles for the site.

1. Overview

In this tutorial, we’ll show how to handle null parameters in Spring Data JPA.

In some cases, when we search for records by parameters, we want to find rows with null as the field value. Other times, we want to ignore a null and skip that field in our query.

Below we’ll show how to implement each of these.

2. Quick Example

Let’s say we have a Customer entity:

@Entity public class Customer < @Id @GeneratedValue private long id; private String name; private String email; public Customer(String name, String email) < this.name = name; this.email = email; >// getters/setters >

Also, we have a JPA repository:

public interface CustomerRepository extends JpaRepository < // method1 // method2 >

We want to search for customers by name and email.

For this purpose, we’ll write two methods that handle null parameters differently.

3. Ways to Handle Null Parameters

First, we’ll create a method that interprets null values of the parameters as IS NULL. Then we’ll create a method that ignores null parameters and excludes them from the WHERE clause.

3.1. IS NULL Query

The first method is very simple to create because null parameters in the query methods are interpreted as IS NULL by default.

List findByNameAndEmail(String name, String email);

Now if we pass a null email, the generated JPQL will include the IS NULL condition:

To demonstrate this, let’s create a test.

First, we’ll add some customers to the repository:

@Before public void before() < entityManager.persist(new Customer("A", "[email protected]")); entityManager.persist(new Customer("D", null)); entityManager.persist(new Customer("D", "[email protected]")); >

Now let’s pass “D” as the value of the name parameter and null as the value of the email parameter to our query method.

We can see that exactly one customer will be found:

List customers = repository.findByNameAndEmail("D", null); assertEquals(1, customers.size()); Customer actual = customers.get(0); assertEquals(null, actual.getEmail()); assertEquals("D", actual.getName());

3.2. Avoid null Parameter With Alternative Methods

Sometimes we want to ignore some parameters and not include their corresponding fields in the WHERE clause.

We can add more query methods to our repository.

For example, to ignore email, we can add a method that only accepts name:

 List findByName(String name);

But this way of ignoring one of our columns scales poorly as the number increases since we would have to add many methods to achieve all the combinations.

3.3. Ignoring null Parameters Using the @Query Annotation

We can avoid creating additional methods by using the @Query annotation and adding a small complication to the JPQL statement:

@Query("SELECT c FROM Customer c WHERE (:name is null or c.name = :name) and (:email is null" + " or c.email = :email)") List findCustomerByNameAndEmail(@Param("name") String name, @Param("email") String email);

Notice that if the email parameter is null, the clause is always true and so doesn’t influence the whole WHERE clause:

:email is null or s.email = :email

Let’s make sure that this works:

List customers = repository.findCustomerByNameAndEmail("D", null); assertEquals(2, customers.size());

We found two customers whose name is “D”, ignoring their emails.

The generated JPQL WHERE clause looks like this:

where (? is null or customer0_.name=?) and (? is null or customer0_.email=?)

With this method, we are putting trust in the database server to recognize the clause regarding our query parameter being null and optimize the execution plan of the query so that it doesn’t have a significant performance overhead. There could be a performance overhead for some queries or database servers, especially involving a huge table scan.

Please note that the above methods for querying null parameters also work if the parameter type is of type – UUID. We can find examples in our Github repo.

4. Conclusion

In this article, we demonstrated how Spring Data JPA interprets null parameters in query methods and how to change the default behaviour.

Perhaps in the future, we’ll be able to specify how to interpret null parameters using the @NullMeans annotation. Notice that it’s a proposed feature at this time and is still under consideration.

To sum up, there are two main ways to interpret null parameters, and they would both be provided by the proposed @NullMeans annotation:

• IS (is null) – the default option demonstrated in Section 3.1.
• IGNORED (exclude a null parameter from the WHERE clause) – achieved either by extra query methods (Section 3.2.) or by using a workaround (Section 3.3.)

As usual, the complete source code is available on GitHub.

Slow MySQL query performance is all too common. Of course it is. A good way to go is, naturally, a dedicated profiler that actually understands the ins and outs of MySQL.

The Jet Profiler was built for MySQL only, so it can do things like real-time query performance, focus on most used tables or most frequent queries, quickly identify performance issues and basically help you optimize your queries.

Critically, it has very minimal impact on your server’s performance, with most of the profiling work done separately — so it needs no server changes, agents or separate services.

Basically, you install the desktop application, connect to your MySQL server, hit the record button, and you’ll have results within minutes:

Источник

Java Essential Tips: How to set null value in Java JDBC PreparedStatement

Target Audience: Java Beginners, JDBC Developers.

What should you know already? : JDBC, PreparedStatement, SQL queries

The Java beginners who works on JDBC should have understanding of what the table and its structure is. It would help to deal with parameters, especially mapping ‘null’ values. Because ‘null’ability of a field should be handled carefully when you pass through Java program.

Passing ‘null’ to String is different from passing ‘null’ to int

Consider the code snippet, in which the SQL query expects 2 parameters: name and address of type VARCHAR.

String query="INSERT INTO person (name, address) VALUES (. )"; PreparedStatement ps=c.prepareStatement(query); // c - java.sql.Connection ps.setString(1, "Ganesh"); ps.setString(2, null); ps.executeUpdate();

The above code works fine and inserts a row with ‘null’ in address column. Now check the following code, which contains one more column contact_number of type BIGINT. This column allows ‘null’. Here trying to insert a person whose contact number is not known ie null, neither be zero.

String query="INSERT INTO person (name, address, contact_number) VALUES (. )"; PreparedStatement ps=c.prepareStatement(query); // c - java.sql.Connection ps.setString(1, "Raja Raman"); ps.setString(2, null); ps.setLong(3, null); // error ps.executeUpdate();

The above code will give a error at line 5. Because the method setLong expects a value of primitive date type long, but here it is ‘null’ type.

How to pass ‘null’ to number column

The correct way to pass ‘null’ to int or long or any number type could be as follows:

String query="INSERT INTO person (name, address, contact_number) VALUES (. )"; PreparedStatement ps=c.prepareStatement(query); // c - java.sql.Connection ps.setString(1, "Raja Raman"); ps.setString(2, null); ps.setNull(3, java.sql.Types.BIGINT); // no error, perfect ps.executeUpdate();

The setNull method gives a flexible way of passing ‘null’ value to almost all kind of data types available in a typical relational database.

Feel free to comment. Happy Programming.

Источник