Sample PreparedStatement usage code in JDBC

In order to prevent the user from using sql statements to inject and attack the database, the sub interface of the Statement interface can be used
java.sql.PreparedStatement extends Statement
This interface represents the object of the precompiled SQL statement, which is precompiled and stored in the PreparedStatement object. You can then use this object to execute the statement efficiently multiple times.  

How to get the implementation class object of PreparedStatement?

Use the method PreparedStatement prepareStatement(String sql) in the Connection interface to create a PreparedStatement object to send the SQL statement to the database.

The prepareStatement method returns the implementation class object of the PreparedStatement interface, which is provided by mysql driver

The code implements a user login case:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Login {
	public static void main(String[] args) {
		/**
		 * Create keyboard entry
		 */
		Scanner sc = new Scanner(System.in);
		System.out.print("enter one user name:");
		String username = sc.nextLine();
		System.out.print("Please input a password:");
		String password = sc.nextLine();
		/**
		 * Get the database connection object by using the method getConnection in the tool class JDBC utils
		 */
		Connection conn = JDBCUtils.getConnection();
		/**
		 * To obtain precompiled executor objects, you can use placeholders to replace the actual parameters in the sql statements to be passed
		 */
		String sql = "SELECT * FROM users WHERE username=? AND PASSWORD = ?";
		/**
		 * Use the method in the PreparedStatement interface to set the actual parameters of the placeholder
		 *  void setObject(int parameterIndex,Object x) 
		 *  Parameters: 
		 *  	int parameterIndex:Which placeholder to set? 1,2,3 
		 *  	Object x:Actual parameters set for placeholders
		 *  be careful: 
		 *  	You must ensure that all placeholders are set with actual parameters. If there are several placeholders, you can call setObject method several times
		 */
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = conn.prepareStatement(sql);
			pst.setObject(1, username);
			pst.setObject(2, password);
			rs = pst.executeQuery();
			/**
			 * Process query results
			 */
			if (rs.next()) {
				System.out.println("Login successful!");
				System.out.println(rs.getString("username") + "\t" + rs.getString("password"));
			} else {
				System.out.println("The user name or password you entered is incorrect!");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pst, conn);
			sc.close();
		}

	}
}
Using PreparedStatement precompiled executor object to add, delete, modify and query the database

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


public class Demo03 {
	public static void main(String[] args) throws Exception {
		/**
		 * Get database connection object
		 */
		Connection conn = JDBCUtils.getConnection();
		/**
		 * Method call to implement corresponding functions
		 */
		insert(conn);
		update(conn);
		delete(conn);
		/**
		 * Common methods in development
		 */
		select(conn);
	}

	/**
	 * Store each row of records found in the database into a javabean object
	 * Store multiple javabean objects in a collection
	 */
	private static void select(Connection conn) throws SQLException {
		/**
		 * Use placeholders to splice select statements
		 */
		String sql = "SELECT * FROM users WHERE username IN(?,?,?)";
		/**
		 * Get precompiled executor object
		 */
		PreparedStatement pst = conn.prepareStatement(sql);
		/**
		 * Set actual parameters for placeholders
		 */
		pst.setObject(1, "java");
		pst.setObject(2, "python");
		pst.setObject(3, "golang");
		/**
		 * Execute sql statement to get query result set
		 */
		ResultSet rs = pst.executeQuery();
		/**
		 * Define a collection to store Users objects
		 */
		ArrayList<Users> list = new ArrayList<Users>();

		while (rs.next()) {
			/**
			 * Take out the results
			 */
			int uid = rs.getInt("uid");
			String username = rs.getString("username");
			String password = rs.getString("password");

			/**
			 * Store each row of records found in the database into a javabean object
			 */
			Users u = new Users(uid, username, password);
			/**
			 * Store objects in a collection
			 */
			list.add(u);
		}

		/**
		 * You can pass this collection to the foreground page to traverse the display for the user
		 */
		for (Users u : list) {
			System.out.println(u);
		}

		/**
		 * Release resources
		 */
		JDBCUtils.close(rs, pst, conn);

	}

	
	/**
	 * Using java program to delete the data of database table
	 */
	private static void delete(Connection conn) throws SQLException {
		//Using placeholders to splice delete statements
		String sql = "DELETE FROM users WHERE uid IN (?,?)";
		//Get precompiled executor object
		PreparedStatement pst = conn.prepareStatement(sql);
		//Set actual parameters for placeholders
		pst.setObject(1, 1);
		pst.setObject(2, 2);
		//Execute sql statements using precompiled executor objects
		int row = pst.executeUpdate();
		//Process result set
		if(row>0){
			System.out.println(row+"Row data,Delete succeeded!");
		}else{
			System.out.println("Data deletion failed!");
		}
		//Release resources
		JDBCUtils.close(null, pst, conn);
	}

	/**
	 * Using java program to modify the data of database table
	 */
	private static void update(Connection conn) throws SQLException {
		//Using placeholders to splice update statements
		String sql = "UPDATE users SET PASSWORD=? WHERE username=?";
		//Get precompiled executor object
		PreparedStatement pst = conn.prepareStatement(sql);
		//Set actual parameters for placeholders
		pst.setObject(1, "Jack Ma");
		pst.setObject(2, "pony ");
		//Execute sql statements using precompiled executor objects
		int row = pst.executeUpdate();
		//Process result set
		System.out.println(row);
		//7. Release resources
		JDBCUtils.close(null, pst, conn);
	}

	/*
	 * Using java program to add data to database table
	 */
	private static void insert(Connection conn) throws SQLException {
		//Using placeholders to splice insert statements
		String sql = "INSERT INTO users(username,PASSWORD) VALUES(?,?)";
		//Get precompiled executor object
		PreparedStatement pst = conn.prepareStatement(sql);
		//Set actual parameters for placeholders
		pst.setObject(1, "Steve Jobs");
		pst.setObject(2, "Bill Gates");
		//Execute sql statements using precompiled executor objects
		int row = pst.executeUpdate();
		//Process result set
		System.out.println(row);
		//Release resources
		JDBCUtils.close(null, pst, conn);
	}
}

Users class

import java.io.Serializable;

/**
 * javabean:Table in database corresponds to class in java
 * 	users Table -- > users class
 * 	Column in table (CID, username, password) -- > member variable in class
 * 	Rows in table -- > users object (multiple rows are stored in a set)
 * 
 * contain:
 * 	1.Private member variable
 * 	2.Public get/set methods
 * 	3.The construction method of empty parameter
 * 	4.toString Method
 * 	5.implements Serializable 
 * 
 */
public class Users implements Serializable {

	private static final long serialVersionUID = 5169686823386234072L;
	private int uid;
	private String username;
	private String password;

	public Users() {
		super();
	}

	public Users(int uid, String username, String password) {
		super();
		this.uid = uid;
		this.username = username;
		this.password = password;
	}

	@Override
	public String toString() {
		return "Users [uid=" + uid + ", username=" + username + ", password=" + password + "]";
	}

	public int getUid() {
		return uid;
	}

	public void setUid(int uid) {
		this.uid = uid;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

}

Tags: SQL Java Database MySQL

Posted on Thu, 30 Apr 2020 05:14:46 -0700 by Spekta