dynamic update query in jdbctemplate update

Dynamic Query In Jdbctemplate Driver For MySQL

Note that the parameter index is 1-based (unlike 0-based index in Java array).

Although the target database system is MySQL, but the same technique can be applied for other database systems as well because the query syntax used is standard SQL which is supported by all relational database systems.

We will learn how to do insert, query, update and delete database records by writing code to manage records of a table Users in a MySQL database called SampleDB.

Table of content: Prerequisites Creating a sample MySQL database Understand the principal JDBC interfaces and classes Connecting to the database Executing INSERT statement Executing SELECT statement Executing UPDATE statement Executing DELETE statement 1.

Prerequisites To begin, make sure you have the following pieces of software installed on your computer: JDK ( download JDK 7 ).

MySQL ( download MySQL Community Server 5.6.12 ).

You may also want to download MySQL Workbench - a graphical tool for working with MySQL databases.

Dynamic Query In Jdbctemplate Driver For MySQL

JDBC Driver for MySQL ( download MySQL ConnectorJ 5.1.25 ).

Extract the zip archive and put the mysql-connector-java-VERSION-bin.jar file into classpath (in a same folder as your Java source files).

Creating a sample MySQL database Lets create a MySQL database called SampleDB with one table Users with the following structure: Execute the following SQL script inside MySQL Workbench: create database SampleDB.

Understand the main JDBC interfaces and classes Lets take an overview look at the JDBCs main interfaces and classes with which we usually work.

They are all available under the java.sql package: DriverManager: this class is used to register driver for a specific database type (e.g.

MySQL in this tutorial) and to establish a database connection with the server via its getConnection() method.

Connection: this interface represents an established database connection (session) from which we can create statements to execute queries and retrieve results, get metadata about the database, close connection, etc.

Statement and PreparedStatement: these interfaces are used to execute static SQL query and parameterized SQL query, respectively.

Statement is the super interface of the PreparedStatement interface.

Their commonly used methods are: boolean execute(String sql): executes a general SQL statement.

It returns true if the query returns a ResultSet, false if the query returns an update count or returns nothing.

ResultSet executeQuery(String sql): executes a SELECT statement and returns a ResultSet object which contains results returned by the query.

A prepared statement is one that contains placeholders (in form question marks ) for dynamic values will be set at runtime.

For example: SELECT from Users WHERE userid Here the value of userid is parameterized by a question mark and will be set by one of the setXXX() methods from the PreparedStatement interface, e.g.

Int(int index, int value).

Use this object to iterate over rows in the result set using next() method, and get value of a column in the current row using getXXX() methods (e.g.

String(), getInt(), getFloat() and so on).

The column value can be retrieved either by index number (1-based) or by column name.

SQLException: this checked exception is declared to be thrown by all the above methods, so we have to catch this exception explicitly when calling the above classes methods.

The following code snippet connects to the database name SampleDB by the user root and password secret: String dbURL jdbc:mysql:localhost:3306sampledb.

In the above code, we have to close the connection explicitly after finish working with the database: conn.close(); However, since Java 7, we can take advantage of the try -with-resources statement which will close the connection automatically, as shown in the following code snippet: try (Connection conn DriverManager.getConnection(dbURL, username, password)).

The sample programs in this tutorial are all using this try-with-resources statement to make a database connection.

NOTE: For details about connecting to a MySQL database, see the article: Connect to MySQL database via JDBC.

To set values for the parameters in the INSERT statement, we use the PreparedStatement s setString() methods because all these columns in the table Users are of type VARCHAR which is translated to String type in Java.

Dynamic Update Query In Jdbctemplate Update