JDBC Encapsulation Learning Note (iii)---object-oriented jdbc using PreparedStatement

Use PreparedStatement object: Why use PreparedStatement
Reason: (1) The use of statement need to splice SQL, too laborious, also prone to error.
String sql = "INSERT INTO UserInfo" + "values (" + player.getid () + ", '" + player.getplayerid () + "', '" + player.getname () + "', '" + player.getteam () + "', '" + player.getcity () + "'," + player.getmaxscore () + ", '" +player.getdraftyear () + "')";

PreparedStatement can pass directly to an SQL statement when it is created, and the field uses placeholders to provide a way to handle placeholders
PreparedStatement PS = connection.preparedstatement (SQL);

String sql = INSERT INTO userinfo values (?,?,?,?). Such an amount of SQL statements is not error-prone, so we used statement to do the update operation, you can use PreparedStatement to replace the
Incoming SQL statements are no longer required in execution ps.executeupdate ()
(2) PreparedStatement can effectively prohibit SQL injection

(3) PreparedStatement is a sub-interface of statement

=============================================================================================================== ===============================================

In Jdbcutils.java we can add a method that implements the following: A SQL statement is passed in, and a mutable parameter is used because it does not know the specific type.

Updating with the PreparedStatement object

public static void Updatedatauseps (String sql, Object ... args) {
Get connections
Connection conn = null;
PreparedStatement PS = null;
ResultSet rs = null;
try {
conn = Jdbcutils.getconnection ();//Get Connected
PS = conn.preparestatement (SQL); Create a statement object
Get the value of a mutable parameter
for (int i = 0; i < args.length; i++) {
Ps.setobject (i + 1, args[i]);
}
Ps.executeupdate ();
SYSTEM.OUT.PRINTLN (SQL);
SYSTEM.OUT.PRINTLN ("SQL execution succeeded");
} catch (ClassNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} finally {
Jdbcutils.closesource (NULL, PS, conn);
System.out.println ("Resource closed successfully");
}

}

This method is then called in the actual operation:

Insert a new athlete, use PreparedStatement

public void TestAddPlayers1 (Players player) {
String sql = "INSERT into userinfo values (?,?,?,?,?,?,?)";
Jdbcutils.updatedatauseps (SQL, Player.getid (), Player.getplayerid (), Player.getname (), Player.getteam (),
Player.getcity (), Player.getmaxscore (), player.getdraftyear ());
}

In the test method, go to call TestAddPlayers1 (Players player) This method:

Test method
@Test
public void Testaddplayers () {
Players player = Getfromconsole ();
TestAddPlayers1 (player);
return player;
}

You can use the JUnit framework to test this, and the console runs as follows:

The full source code looks like this:

Package Com.jdbc.basedemo;

Import java.io.IOException;
Import java.sql.Connection;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import Java.util.Scanner;

Import Org.junit.Test;

Import Com.jdbc.bean.Players;

public class Crudplayer {
Add an athlete and pass in an athlete object
public void Addplayers (Players player) {
Gives a SQL
String sql = "INSERT INTO UserInfo" + "values (" + player.getid () + ", '" + player.getplayerid () + "', '"
+ player.getname () + "', '" + player.getteam () + "', '" + player.getcity () + "'," + Player.getmaxscore ()
+ ", '" + player.getdraftyear () + "')";
SYSTEM.OUT.PRINTLN (SQL);
Call Update method
Jdbcutils.updatedata (SQL);
}

@ Insert a new athlete, use PreparedStatement

public void TestAddPlayers1 (Players player) {
String sql = "INSERT into userinfo values (?,?,?,?,?,?,?)";
Jdbcutils.updatedatauseps (SQL, Player.getid (), Player.getplayerid (), Player.getname (), Player.getteam (),
Player.getcity (), Player.getmaxscore (), player.getdraftyear ());
}

Test method
@Test
public void Testaddplayers () {
Players player = Getfromconsole ();
TestAddPlayers1 (player);
return player;
}

Getting the input athlete information from the console
Private Players Getfromconsole () {
TODO auto-generated Method Stub
Scanner input = new Scanner (system.in);
Players player = new Players ();
System.out.println ("Please enter player information");
This is because the primary key is set up in the data table, so the concatenation of the SQL is only obtained, without having to enter the ID again
System.out.print ("Player ID:");
Player.setplayerid (Input.next ());
System.out.print ("Player name:");
Player.setname (Input.next ());
System.out.print ("Team name:");
Player.setteam (Input.next ());
System.out.print ("Team City:");
Player.setcity (Input.next ());
System.out.print ("Individual single field Highest score:");
Player.setmaxscore (Input.nextint ());
SYSTEM.OUT.PRINTLN ("Draft year:");
Player.setdraftyear (Input.next ());

return player;
}

Query type
@Test
public void Testgetplayer () {
Get query type
int searchtype = Gettypefromconsole ();
Execute Query
Players Players = Searchplayer (searchtype);
Print athlete Information
Printplayer (players);

return players;
}

Print athlete Information
private void Printplayer (Players Players) {
TODO auto-generated Method Stub
if (players! = null) {
System.out.println (players);
} else {
System.out.println ("No person ...");
}
}

Information method of specific query athlete
Private Players searchplayer (int searchtype) {
TODO auto-generated Method Stub
SELECT * is not a good writing, here for lazy
String sql = "SELECT * from UserInfo" + "where";
Scanner Scanner = new Scanner (system.in);
Prompts the user to enter the type of the query according to the type determined
Final confirmation SQL statement
if (searchtype = = 1) {
System.out.print ("Please enter player ID number:");
String playerID = Scanner.next ();
sql = SQL + "playid=" + playerID + "'";
} else {
System.out.print ("Please enter player name:");
String name = Scanner.next ();
sql = SQL + "Username=" + name + "'";
}
Execute Query
Players player = getplayer (SQL);

return player;
}

Execute the query by executing the query according to the SQL statement
Private Players Getplayer (String sql) {
TODO auto-generated Method Stub
Players player = null;
Connection conn = null;
Statement Statement = null;
ResultSet rs = null;

try {
conn = Jdbcutils.getconnection ();
statement = Conn.createstatement ();
rs = statement.executequery (SQL);
if (Rs.next ()) {
Player = new Players (Rs.getint (1), rs.getstring (2), rs.getstring (3), rs.getstring (4), rs.getstring (5),
Rs.getint (6), rs.getstring (7));
int id = rs.getint (1);
String Playid = rs.getstring (2);
String name = rs.getstring (3);
String team = rs.getstring (4);
String City = rs.getstring (5);
int maxscore = Rs.getint (6);
String draftyear = rs.getstring (7);

SYSTEM.OUT.PRINTLN ("ID:" + ID);
System.out.println ("Playid:" + Playid);
System.out.println ("Name:" + name);
System.out.println ("Team:" + team);
System.out.println ("Cities:" + City);
SYSTEM.OUT.PRINTLN ("Individual single field highest score:" + Maxscore);
SYSTEM.OUT.PRINTLN ("Draft year:" + draftyear);

}
} catch (ClassNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} finally {
Jdbcutils.closesource (RS, statement, Conn);
}
SYSTEM.OUT.PRINTLN (SQL);
return player;
}

Reads an integer from the console, returns 1, queries with the player ID, 2, queries with names, other invalid and prompts the user to re-enter
private static int Gettypefromconsole () {
TODO auto-generated Method Stub
1 Follow the prompts to let the user enter the query type, return 1, with the Player ID query, 2, with the name of the query
System.out.println ("Please enter query type: 1. Use player ID query, 2. Use name query");
Scanner input = new Scanner (system.in);
int type = Input.nextint ();
Judge
if (type! = 1 && Type! = 2) {
SYSTEM.OUT.PRINTLN ("Input not valid, please re-enter ....");
throw new RuntimeException (); Interrupt Program

}
return type;
}

}

=============================================================================================================== ==================================================

Jdbcutils.java Source code files:

Package Com.jdbc.basedemo;

Import java.io.IOException;
Import Java.io.InputStream;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.Properties;

/*
* Operate the public class of the database, there are some public methods
* */
public class Jdbcutils {
Get database connection
Connecting to a database

public static Connection getconnection () throws ClassNotFoundException, IOException, SQLException {
Defining related variables
String driverclass = null;
String jdbcurl = null;
String user = null;
String password = null;

Read configuration file Jdbc.properties
InputStream in = JDBCUtils.class.getClassLoader (). getResourceAsStream ("jdbc.properties");

Properties Properties = new properties ();
Properties.load (in);
Driverclass = Properties.getproperty ("Driver");
Jdbcurl = Properties.getproperty ("Jdbcurl");
user = Properties.getproperty ("user");
Password = properties.getproperty ("password");
Load Database Driver
Class.forName (Driverclass);
Get connections
Connection conn = drivermanager.getconnection (jdbcurl, user, password);
Return conn;
}

Ways to release resources
public static void Closesource (ResultSet rs, Statement Statement, Connection conn) {
if (rs! = null) {
try {
Rs.close ();
} catch (Exception e) {
Todo:handle exception
E.printstacktrace ();
}
}
if (statement! = NULL) {
try {
Statement.close ();
} catch (Exception e) {
Todo:handle exception
E.printstacktrace ();
}
}
IF (conn! = null) {
try {
Conn.close ();
} catch (Exception e) {
Todo:handle exception
E.printstacktrace ();
}
}
}

Encapsulates a common Update method for Insert,update,delete
public static void UpdateData (String sql) {
Get connections
Connection conn = null;
Statement Statement = null;
ResultSet rs = null;
try {
conn = Jdbcutils.getconnection ();//Get Connected
statement = Conn.createstatement (); Create a statement object
Statement.executeupdate (SQL);
SYSTEM.OUT.PRINTLN (SQL);
SYSTEM.OUT.PRINTLN ("SQL execution succeeded");
} catch (ClassNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} finally {
Jdbcutils.closesource (RS, statement, Conn);
System.out.println ("Resource closed successfully");
}

}
Updating with the PreparedStatement object

public static void Updatedatauseps (String sql, Object ... args) {
Get connections
Connection conn = null;
PreparedStatement PS = null;
ResultSet rs = null;
try {
conn = Jdbcutils.getconnection ();//Get Connected
PS = conn.preparestatement (SQL); Create a statement object
Get the value of a mutable parameter
for (int i = 0; i < args.length; i++) {
Ps.setobject (i + 1, args[i]);
}
Ps.executeupdate ();
SYSTEM.OUT.PRINTLN (SQL);
SYSTEM.OUT.PRINTLN ("SQL execution succeeded");
} catch (ClassNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} finally {
Jdbcutils.closesource (NULL, PS, conn);
System.out.println ("Resource closed successfully");
}

}
}

JDBC Encapsulation Learning Note (iii)---object-oriented jdbc using PreparedStatement