Prepared statement in java mysql

How to use prepared statement for select query in Java with MySQL?

Now you can display all records of the table using Java PreparedStatement. You need to use the executeQuery() method.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class PreparedStatementSelectQueryDemo < public static void main(String[] args) < String JdbcURL = "jdbc:mysql://localhost:3306/sample?useSSL=false"; String Username = "root"; String password = "123456"; Connection con = null; PreparedStatement pstmt = null; ResultSet rst = null; String myQuery = "select Id,Name,Age from JavaPreparedStatement"; try < con = DriverManager.getConnection(JdbcURL, Username, password); pstmt = con.prepareStatement(myQuery); rst = pstmt.executeQuery(); System.out.println("Id\t\tName\t\tAge
"); while(rst.next()) < System.out.print(rst.getInt(1)); System.out.print("\t\t"+rst.getString(2)); System.out.print("\t\t"+rst.getInt(3)); System.out.println(); >> catch(Exception exec) < exec.printStackTrace(); >> >

Here is the snapshot of Java code −

Here is the snapshot of sample output −

Learning faster. Every day.

Источник

Java JDBC – How to Use PreparedStatement in Programming

The PreparedStatement object is derived from Statement class. Statement is used for sending and executing query in database but it is more convenient to use PreparedStatement to send and execute command in database. PreparedStatement uses parameterized query and keeps precompiled sql statement so it is faster than Statement class because it is compiled once and can be used multiple times.

PreparedStatement ps=con.prepareStatement("INSERT INTO ITEM VALUES(. )");

PreparedStatement is used to execute parameterized query. It uses ? parameter at the place of value and later store value in it. As we initialize PreparedStatement, it gets compiled and then can be used so many times directly. Once database engine compiles PreparedStatement, It doesn’t get compiled next time and executed directly. It makes it much efficient and faster than Statement class.

Method	Description
executeQuery	This method executes sql query in PreparedStatement and returns ResultSet.
updateQuery	This method executes sql query in PreparedStatement and used for Only with Insert, Update or Delete command or sql statement that return nothing.
public void setInt(int paramIndex, int value)	sets the integer value to the given parameter index.
public void setString(int paramIndex, String value)	sets the String value to the given parameter index.
public void setFloat(int paramIndex, float value)	sets the float value to the given parameter index.
public void setDouble(int paramIndex, double value)	sets the double value to the given parameter index.

INSERT RECORD using PreparedStatement

package JavaStatements; import java.sql.*; public class PreparedStatement_Example < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/STOREDB"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; //Statement stmt = null; PreparedStatement ps=null; try < //Step 1 : Connecting to server and database con = DriverManager.getConnection(dburl, dbuser, dbpass); //Step 2 : Initialize PreparedStatement ps= con.prepareStatement("INSERT INTO ITEM(PRODUCT,PRICE) VALUES(. )"); //Step 3 : Set Value for ? Parameter //executing 1st query ps.setString(1,"Java eBook"); ps.setString(2, "460"); ps.executeUpdate(); System.out.println("Java eBook Stored Successfully"); //executing 2nd query ps.setString(1, "JDBC eBook"); ps.setString(2, "830"); ps.executeUpdate(); System.out.println("JDBC eBook Stored Successfully"); >catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

Java eBook Stored Successfully
JDBC eBook Stored Successfully
Closing the connection.

UPDATE RECORD using PreparedStatement

package JavaStatements; import java.sql.*; public class PreparedStatement_Example < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/STOREDB"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; //Statement stmt = null; PreparedStatement ps=null; try < //Step 1 : Connecting to server and database con = DriverManager.getConnection(dburl, dbuser, dbpass); //Step 2 : Initialize PreparedStatement ps= con.prepareStatement("UPDATE ITEM SET PRODUCT = ? WHERE //Step 3 : Set Value for ? Parameter //executing 1st query ps.setString(1,"MySQL eBook"); ps.setInt(2, 13); ps.executeUpdate(); System.out.println("MySQL eBook Updated Successfully"); >catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

MySQL eBook Updated Successfully
Closing the connection.

DELETE RECORD using PreparedStatement

package JavaStatements; import java.sql.*; public class PreparedStatement_Example < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/STOREDB"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; //Statement stmt = null; PreparedStatement ps=null; try < //Step 1 : Connecting to server and database con = DriverManager.getConnection(dburl, dbuser, dbpass); //Step 2 : Initialize PreparedStatement ps= con.prepareStatement("DELETE FROM ITEM WHERE //Step 3 : Set Value for ? Parameter ps.setInt(1, 13); ps.executeUpdate(); System.out.println("MySQL eBook Deleted Successfully"); >catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

MySQL eBook Deleted Successfully
Closing the connection.

SELECT Record in ResultSet using PreparedStatement

package JavaStatements; import java.sql.*; public class PreparedStatement_Example < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/STOREDB"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; //Statement stmt = null; PreparedStatement ps=null; try < //Step 1 : Connecting to server and database con = DriverManager.getConnection(dburl, dbuser, dbpass); //Step 2 : Initialize PreparedStatement ps= con.prepareStatement("SELECT * FROM ITEM"); ResultSet rset=ps.executeQuery(); while(rset.next()) < System.out.println("ID : " + rset.getInt(1) + ", PRODUCT : " + rset.getString(2) + ", PRICE : " + rset.getString(3)); >> catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

ID : 1, PRODUCT : MousePad, PRICE : 190
ID : 2, PRODUCT : Stationary, PRICE : 2870
ID : 3, PRODUCT : Books, PRICE : 765
ID : 4, PRODUCT : HardDisk, PRICE : 3887
ID : 5, PRODUCT : Ram, PRICE : 953
ID : 6, PRODUCT : Printer, PRICE : 8746
ID : 7, PRODUCT : Keyboard, PRICE : 646
ID : 8, PRODUCT : Mouse, PRICE : 947
ID : 9, PRODUCT : HDMI CABLE, PRICE : 850
ID : 11, PRODUCT : Charger, PRICE : 1800
ID : 12, PRODUCT : MainJava Logo, PRICE : 900
ID : 14, PRODUCT : JDBC eBook, PRICE : 830
Closing the connection.

Summary

In this tutorial you learned how to use PreparedStatement for Storing, Retrieving, Updating or Deleting row in MySQL table using JDBC. This chapter includes all the programming examples that demonstrate all the theory well.

Источник

JDBC PreparedStatement — Insert a Record Example

PreparedStatement interface provides the executeUpdate() method — executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

package com.javaguides.jdbc.preparestatement.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * Insert PrepareStatement JDBC Example * * @author Ramesh Fadatare * */ public class InsertPStatementExample < private static final String INSERT_USERS_SQL = "INSERT INTO users" + " (id, name, email, country, password) VALUES " + " (?, ?, ?, ?, ?);"; public static void main(String[] argv) throws SQLException < InsertPStatementExample createTableExample = new InsertPStatementExample(); createTableExample.insertRecord(); > public void insertRecord() throws SQLException < System.out.println(INSERT_USERS_SQL); // Step 1: Establishing a Connection try (Connection connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root"); // Step 2:Create a statement using connection object PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) < preparedStatement.setInt(1, 1); preparedStatement.setString(2, "Tony"); preparedStatement.setString(3, "tony@gmail.com"); preparedStatement.setString(4, "US"); preparedStatement.setString(5, "secret"); System.out.println(preparedStatement); // Step 3: Execute the query or update query preparedStatement.executeUpdate(); > catch (SQLException e) < // print SQL exception information printSQLException(e); > // Step 4: try-with-resource statement will auto close the connection. > public static void printSQLException(SQLException ex) < for (Throwable e: ex) < if (e instanceof SQLException) < e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) < System.out.println("Cause: " + t); t = t.getCause(); > > > > >

Output

JDBC PreparedStatement — Update a Record Example

In the previous example, we have inserted a record into the users table and now we will update the same record with below Java program:

package com.javaguides.jdbc.preparestatement.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * Update PreparedStatement JDBC Example * @author Ramesh Fadatare * */ public class UpdatePStatementExample < private static final String UPDATE_USERS_SQL = "update users set name = ? where style="box-sizing: border-box;">"; public static void main(String[] argv) throws SQLException < UpdatePStatementExample updateStatementExample = new UpdatePStatementExample(); updateStatementExample.updateRecord(); > public void updateRecord() throws SQLException < System.out.println(UPDATE_USERS_SQL); // Step 1: Establishing a Connection try (Connection connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root"); // Step 2:Create a statement using connection object PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) < preparedStatement.setString(1, "Ram"); preparedStatement.setInt(2, 1); // Step 3: Execute the query or update query preparedStatement.executeUpdate(); > catch (SQLException e) < // print SQL exception information printSQLException(e); > // Step 4: try-with-resource statement will auto close the connection. > public static void printSQLException(SQLException ex) < for (Throwable e: ex) < if (e instanceof SQLException) < e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) < System.out.println("Cause: " + t); t = t.getCause(); > > > > >

Output

JDBC PreparedStatement — Select a Record Example

In the previous example, we have inserted or updated a record into the users table and now we will query a list of users from the database table by id.

package com.javaguides.jdbc.preparestatement.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * Select PreparedStatement JDBC Example * * @author Ramesh Fadatare * */ public class SelectPStatementExample < private static final String QUERY = "select id,name,email,country,password from Users where style="box-sizing: border-box;">"; public static void main(String[] args) < // using try-with-resources to avoid closing resources (boiler plate code) // Step 1: Establishing a Connection try (Connection connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/jdbc_demo?useSSL=false", "root", "root"); // Step 2:Create a statement using connection object PreparedStatement preparedStatement = connection.prepareStatement(QUERY);) < preparedStatement.setInt(1, 1); System.out.println(preparedStatement); // Step 3: Execute the query or update query ResultSet rs = preparedStatement.executeQuery(); // Step 4: Process the ResultSet object. while (rs.next()) < int id = rs.getInt("id"); String name = rs.getString("name"); String email = rs.getString("email"); String country = rs.getString("country"); String password = rs.getString("password"); System.out.println(id + "," + name + "," + email + "," + country + "," + password); > > catch (SQLException e) < printSQLException(e); >// Step 4: try-with-resource statement will auto close the connection. > public static void printSQLException(SQLException ex) < for (Throwable e: ex) < if (e instanceof SQLException) < e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) < System.out.println("Cause: " + t); t = t.getCause(); > > > > >

JDBC PreparedStatement — Delete a Record Example

package com.javaguides.jdbc.preparestatement.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * Delete Statement JDBC Example * * @author Ramesh Fadatare * */ public class DeletePreparedStatementExample < private static final String DELETE_USERS_SQL = "delete from users where style="box-sizing: border-box;">"; public static void main(String[] argv) throws SQLException < DeletePreparedStatementExample deleteStatementExample = new DeletePreparedStatementExample(); deleteStatementExample.deleteRecord(); > public void deleteRecord() throws SQLException < System.out.println(DELETE_USERS_SQL); // no need to register driver manually // Step 1: Establishing a Connection try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_demo?useSSL=false", "root", "root"); // Step 2:Create a statement using connection object PreparedStatement statement = connection.prepareStatement(DELETE_USERS_SQL);) < statement.setInt(1, 1); // Step 3: Execute the query or update query int result = statement.executeUpdate(); System.out.println("Number of records affected :: " + result); > catch (SQLException e) < // print SQL exception information printSQLException(e); > // Step 4: try-with-resource statement will auto close the connection. > public static void printSQLException(SQLException ex) < for (Throwable e: ex) < if (e instanceof SQLException) < // e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) < System.out.println("Cause: " + t); t = t.getCause(); > > > > >

delete from users where of records affected :: 1

Conclusion

In this tutorial, we have seen how to use JDBC PreparedStatement to insert, select, update and delete records with MySQL database.

Источник