What is the best way to create Statement and ResultSet in java
Conclusion From the above article, we have seen different types of statements with their basic syntax, and we also saw different examples of JDBC statements. ExecuteUpdate string SQL statement: This method, by and large, returns the number of lines influenced by the SQL statement’s execution.
What is the best way to create Statement and ResultSet in java
Should I initialize Statement and ResultSet in try-with- resources Statement or just initialize in try block and close Statement and ResultSet in finally block.
I need to understand the best way to implement this according to the best practices
try-with-resources Statement
try(Connection connection = DriverManager.getConnection("url"); Statement st = connection.createStatement(); ResultSet rs = st.executeQuery("query")) < >catch (SQLException e) or try-catch with finally
Statement st = null; ResultSet rs = null; try(Connection connection = DriverManager.getConnection("url")) < st = connection.createStatement(); rs = st.executeQuery("query"); >catch (SQLException e) < e.printStackTrace(); >finally < try < rs.close(); st.close(); >catch (SQLException e) < e.printStackTrace(); >> I believe that most of the time less code is more clean code.Secondly readability does matter if you are working with a team and your team member can not read your code properly than it’s definitely your fault.
In first case its look quite simple and easily understandable for the programmer who have worked with Java7 and removes the boiler plate code of closing the resources.In the second case you first initialized with null and have finally block with try-close-catch session.It is always better to go with the clean code which is the first one and note one more thing,
In a try-with-resources statement, any catch or finally block is run after the resources declared have been closed.
Problem with try-with-resource is you can not use the resources out of the scope of try it will be limited to try block while that’s not the case in your second part.
JDBC Tutorial — GeeksforGeeks, Load and register the JDBC drivers. First load then register the same Establish the connection Create a statement Execute the query Process the results Close the connections Let us discuss the above steps by picking up cases from the real world to get a clear sake of understanding our concept.
Best design to create and then use the JDBC statement in threading
I am working in an application, where huge number and various kinds of data need to be populated in DB2 tables, in a sufficient amount of time. So I made changes in code to populated the data in different Threads, like this
public class ThreadPopulator implements Runnable < volatile private boolean isTaskCompleted; volatile private Connection db2Conn; volatile private Listlist; volatile private String srcLib; public ThreadPopulator(Connection db2Conn, List list, String srcLib) < this.db2Conn = db2Conn; this.list = list; this.srcLib = srcLib; >public void run() < try < isTaskCompleted = false; execute(srcLib, list); >catch (Throwable e) < >> synchronized private void execute(String srcLib, List list) < PreparedStatement stmt = null; int **** = list.size(); for (int i = 0; i < ****; i++) < try < if (stmt == null) stmt = db2Conn.prepareStatement("INSERT INTO DATA VALUES(?, ?, ?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT); Data data = list.get(i); stmt.setString(1, srcLib); stmt.setString(2, "VV"); stmt.setDouble(3, data.getSeq()); stmt.setInt(4, data.getDate()); stmt.addBatch(); if ((i + 1) % 5000 == 0) stmt.executeBatch(); // Execute every 5000 items. >catch (Exception e) < >> try < if (**** >0) stmt.executeBatch(); //for remaining records > catch (Exception e) < >finally < stmt.close(); if (list != null) list.clear(); isTaskCompleted = true; >> public static ThreadPopulator insert(Connection db2Conn, ArrayList list, String srcLib) < ThreadPopulator populator = new ThreadPopulator(db2Conn, srcLib); Thread thread = new Thread(populator); thread.start(); return populator; >> Then I got the error: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH21E . Means number of opened statements exceeds the limit which is around 13K, so I made some logging to see how many statement are being created (by incrementing a static counter on prepareStatement() and by decrementing on close()), and found no decrementing is taking place, since close() is not called and the total statements count is reached to 22K.
So I finally made some changes to create a static statement object and to use that instance in all threads as
private static PreparedStatement stmt = null; public static void createStatement(Connection db2Conn) < try < if (stmt == null) < stmt = db2Conn.prepareStatement("INSERT INTO DATA VALUES(?, ?, ?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT); >> catch (Exception e) < >> Now everything is working fine. But as a Java developer, I am not happy with this technique. i.e. Creating a static variable and then use that object in all threads.
Could you please suggest me what would be the best design to create/use the statement in threading and also it works for my case as well.
The common practice in these situations is to use some sort of connection pool; the database driver needs to implement this feature. You can find info on the DB2 implementation at IBM’s db2 documentation .
Java — createStatement method in jdbc, The JDBC API enables you to write Java code independent of the specific database server used. Whenever you switch of DB, you just have to switch the JDBC driver (and possibly also change the SQL statements whenever they contain DB-server specific SQL language, but not the Java code). Share Improve …
JDBC Statement
Introduction to JDBC Statement
Basically, Java provides different types of interface to the user, in which the Java language provides JDBC statement s. Normally a JDBC Statement is used to execute the different queries of the database. In other words, we can say that a JDBC statement is a bunch of ResultSet, and we can use it as a method to get the desired object of the ResultSet. In a JDBC statement, first, we need to connect with the database; after successfully making the connection, we can execute the different JDBC statements with the database, such as CallableStatement and PreparedStatement as per user requirement .
Different JDBC Statements
Given below are the different types of JDBC statements:
1. Creating Statement Object
Basically, creating a statement is used to execute the different SQL statements as per user requirements . In this statement, we establish the connection objects by using the createstatement () method.
Statement stmt_obj = null;
Try
<
stmt_obj = con_obj.createStatement();
. . . .
. . . .
>
catch (SQLException e_obj)
<
. . . . . . . . .
>
Explanation:
In the above syntax, we use the createStatement() method to establish the connection with the database. Then, we can perform the different SQL statement s as per user requirements listed below.
- Boolean string SQL statement: This method, for the most part, returns a Boolean value of valid on the off chance that a ResultSet object can be recovered, assuming not, this by and large and returns bogus. This technique is by, and large used to execute SQL DDL explanations or now and again when the client needs to utilize genuinely powerful SQL .
- ExecuteUpdate string SQL statement: This method, by and large, returns the number of lines influenced by the SQL statement’s execution. It is likewise used to execute SQL statements for which the client or software engineer hopes to get various columns influenced .
- ExecuteQuery ResultSet string SQL statement: Basically, this method is used to return the ResultSet object as per the user requirement. Basically, this method is used when the user requires the specified result set.
- Closing Statement Object: Soon after, the client closes Connection objects to save information base assets, and this is the very explanation for which the client or the software engineer should close the Statement Object .
To close this, a basic call to the close () method will do the greater part of the work. Kindly note that if the client shuts the connection object first, then it will close the statement object too. Thus, item in request to guarantee legitimate cleanup, the client should expressly close the statement object in every case.
Explanation:
- In the above syntax, we used the close () method to close the connection. After the connection setup, we perform the different SQL statements, and at the end of the SQL statement, we must close the connection.
2. Prepared Statement
This statement is used to recompile the SQL statement. So that recompile statement we can execute multiple times as per user requirement. Basically, the prepared statement uses the SQL statement.
Let’s see how we can implement the prepared statement as follows.
Basically, there is a three-way to execute the PreparedStatement as follows:
- Execute(): Basically, this is used to execute the static SQL statement that is available in the prepared statement object as well as it also returns the Boolean values.
- executeQuery(): It is used to return the ResultSet from the current prepared statement that is available.
- executeUpdate(): Returns the number of columns influenced by the DML explanations like INSERT, DELETE, and more that is available in the current Prepared Statement.
preparedStatement p_stmt_obj = null;
try
<
String SQL_Stmt = “update stude set colm name= required value where colm name= required value”;
P_stmt_obj = con_obj.prepareStatement(SQL_Stmt);
. . . . .
>
catch(SQLException e_obj)
)
<
>
Explanation:
- In the above syntax, we use preparedStatement to execute the DML statement such as insert, delete, etc., as per user requirement as shown in the above syntax.
3. Callable Statement
It is utilized to put away techniques which are a gathering of articulations that we aggregate in the information base for some undertaking, they are helpful when we are managing numerous tables with complex situations, and as opposed to sending various questions to the data set, we can send the necessary information to the put-away method and lower the rationale executed in the data set worker itself. The Callable statement interface given by JDBC API helps in executing the put-away methodology.
callableStatement call_stmt = null;
try
<
String SQL_Call_Stmt = “”;
Call_stmt = con_obj.prepareCall(SQL_Call_Stmt)
>
catch (SQLException e_obj)
<
. . .
>
Explanation:
- In the above syntax, we used a stored procedure representing SQL statements, as shown in the above syntax.
Example of JDBC Statement
Given below is the example of DBC statement:
import java.sql.*;
class demo_create <
public static void main(String[] args)
<
try <
Class.forName(«com.mysql.jdbc.Driver»);
Connection con_obj = DriverManager.getConnection(
«jdbc:mysql://localhost», «root», «root»);
Statement c_stmt = con_obj.createStatement();
String sql_stmt = «select * from stud»;
ResultSet r_stmt = c_stmt.executeQuery(sql_stmt);
while (r_stmt.next()) <
System.out.println(
«Stud_Name: » + r_stmt.getString(«name»));
System.out.println(
«Dept:» + r_stmt.getString(«dept»));
>
>
catch (SQLException e_obj) <
System.out.println(e_obj);
>
>
>
Explanation:
- In the above example, we try to implement a create statement in jdbc . In the above example, first, we establish a connection with the database by using the Class.forName statement as shown.
- After connection, we can fetch the records from the database. Here we try to display the name of students and departments of students. We illustrated the final output or result of the above program using the following screenshot as follows.
Similarly, we can execute all statements.
Conclusion
From the above article, we have seen different types of statements with their basic syntax, and we also saw different examples of JDBC statements. From this article, we saw how and when we use the JDBC statement.
Final thoughts
This is a guide to JDBC Statement. Here we discuss the introduction, different JDBC statements and example for better understanding. You may also have a look at the following articles to learn more –
JDBC Tutorial, The java.sql and javax.sql are the primary packages for JDBC 4.0. This is the latest JDBC version at the time of writing this tutorial. It offers the main classes for interacting with your data sources. The new features in these packages include changes in the following areas −. Automatic database driver loading. Exception …