[Java] operations on the database (including preprocessing in the database)

[Java] operations on the database (including preprocessing in the database)
I. connection problems

I just introduced how to connect to the database and write the corresponding template, but its maintainability is very poor. How can I solve this problem?

First, write a configuration file jdbc. properties.

## MySQLdriver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/hncu?useUnicode=true&characterEncoding=UTF-8username=rootpassword=1234## Oracle#driver=oracle.jdbc.driver.OracleDriver#url=jdbc:oracle:thin:@192.168.31.12:1521:orcl#username=scott#password=tiger

Then create a factory ConnFactory. java that generates the connection

 

 

Package cn. hncu. hibernate0; import java. io. IOException; import java. SQL. connection; import java. SQL. driverManager; import java. util. properties; public class ConnFactory {private static Connection conn; static {try {// read the configuration file Properties p = new Properties (); p. load (ConnFactory. class. getClassLoader (). getResourceAsStream ("jdbc. properties "); String driver = p. getProperty ("driver"); String url = p. getProperty ("url"); String username = p. getProperty ("username"); String pwd = p. getProperty ("password"); Class. forName (driver); conn = DriverManager. getConnection (url, username, pwd); System. out. println ("connected to the database... "+ conn);} catch (Exception e) {throw new RuntimeException (" failed to read the configuration file ", e) ;}} public static Connection getConn () {return conn ;} public static void main (String [] args) {getConn ();}}

Finally, it is obtained directly through ConnFactory. getConn. The advantage of this is that when you change the type of the database to be connected, you only need to modify the content in the configuration file.

 

2. Explain the getXXX () method

 

@ Testpublic void getXXXDemo () throws Exception {Statement st = ConnFactory. getConn (). createStatement (); String SQL = "select * from book"; ResultSet rs = st.exe cuteQuery (SQL); while (rs. next () {Integer id = rs. getInt (1); // here, 1 indicates the first column in the data table. The same String name = rs is shown below. getString (2); // Double price = (Double) rs. getObject (3); // an exception occurs because BigDecimal is used internally to process Double price = rs. getDouble (3); Object dateTime = rs. getObject (4); // read the date and time as a whole System. out. println (id + "," + name + "," + price + "," + dateTime); String strDateTime = dateTime. toString (); System. out. println (strDateTime); strDateTime = rs. getDate (4) + "--" + rs. getTime (4); // Date and time can be separately obtained by System. out. println (":" + strDateTime);} ConnFactory. getConn (). close ();}

Note: For auto-increment columns, new records are inserted after deletion. The sequence number does not go back and continues to grow. That is, there will be an empty number in the middle

 

Iii. Three executeXXX () methods in Statement

1. executeQuery: only select statements can be executed.

2. executeUpdate: The insert, delete, and update statements can be executed, but the select statement cannot be executed.

3. execute: Four (any) statements can be executed for addition, deletion, modification, and query. If this method returns false when a non-select statement is executed, true is returned when the select statement is executed, and the st object caches the query results.

You can use ResultSet rs = st. getResultSet () to obtain the result set.

@ Testpublic void executeXXXDemo () throws Exception {Statement st = ConnFactory. getConn (). createStatement (); String SQL = "select * from book"; // String SQL = "insert into book (name, price, pub) values ('Software Project', 22.35, '2017-12-05 22:12:23 ') "; // String SQL =" update book set price = 2015 where name = 'Software project '"; // String SQL = "delete from book where name = 'project'"; // st.exe cuteQuery (SQL); // st.exe cuteUpdate (SQL ); boolean boo = st.exe cute (SQL); if (boo) {ResultSet rs = st. getResultSet (); while (rs. next () {System. out. println (rs. getObject (2 ));}}}

 

Iv. Anti-Black Technology for database queries (pre-processing statements)

Case and User Logon (it is dangerous to concatenate SQL statements through user input)

 

@ Test // User Logon public void login () throws Exception {Connection con = ConnFactory. getConn (); Statement st = con. createStatement (); pipeline SC = new pipeline (System. in); int id = SC. nextInt (); SC. nextLine (); String name = SC. nextLine (); String SQL = "select count (*) from stud where id =" + id + "and sname = '" + name + "'"; System. out. println ("SQL:" + SQL); ResultSet rs = st.exe cuteQuery (SQL); rs. next (); int a = rs. getInt (1); if (a <= 0) {System. out. println ("Logon Failed");} else {System. out. println ("Logon successful");} con. close ();}

Black method, input: 1002 (Press ENTER) 1 'or '1' = '1
Therefore, if you need to splice SQL statements with user input information, you cannot use statement. Otherwise, you can build a true condition in the where clause to break through the protection.

 

Should be solved with PreparedStatement!

@ Test // User Logon Black: 1002 (Press ENTER) 1 'or '1' = '1public void login2 () throws Exception {login SC = new Login (System. in); Connection con = ConnFactory. getConn (); String SQL = "select count (*) from stud where id =? And sname =? "; // A placeholder ('? '), And then assign a value to the placeholder PreparedStatement pst = con. prepareStatement (SQL); // you can specify the int id = SC. nextInt (); SC. nextLine (); pst. setInt (1, id); // parameter 1 ---- represents the 1st placeholder String name = SC. nextLine (); pst. setString (2, name); // parameter 2 ResultSet rs = pst.exe cuteQuery (); rs. next (); int a = rs. getInt (1); if (a <= 0) {System. out. println ("Logon Failed");} else {System. out. println ("Logon successful");} con. close ();}

 

5. Get auto-increment columns (such as id)

@ Test // demonstrate how to obtain the auto-increment column, such as idpublic void saveAuto () throws Exception {Connection con = ConnFactory. getConn (); String SQL = "insert into book (name, price, pub) values ('javaee ', 100.8, '2017-06-12 08:30:30 ')"; statement st = con. createStatement (); // st.executeupdate(sql1_1_st.exe cuteUpdate (SQL, Statement. RETURN_GENERATED_KEYS); ResultSet rs = st. getGeneratedKeys (); // it encapsulates all automatically generated values if (rs. next () {int id = rs. getInt (1); // get 1st System. out. println ("Automatically increasing id:" + id);} System. out. println ("-----------------"); // pre-processing statement SQL = "insert into book (name, price) values (?,?) "; PreparedStatement pst = con. prepareStatement (SQL, Statement. RETURN_GENERATED_KEYS); pst. setString (1, "Computer Basics"); pst. setDouble (2, 281_1_pst.exe cuteUpdate (); ResultSet rs2 = pst. getGeneratedKeys (); // it encapsulates all automatically generated values if (rs2.next () {int id = rs2.getInt (1); // gets 1st auto-increment columns System. out. println ("Automatically increasing id:" + id );}}

6. Demonstrate batch processing statements

 

Public void batch () throws Exception {Connection con = ConnFactory. getConn (); String SQL = "insert into book (name, price, pub) values ('javaee ', 100.8, '2017-06-12 08:30:30 ')"; statement st = con. createStatement (); for (int I = 0; I <10; I ++) {st. addBatch (SQL);} SQL = "update book set price = price * 1.1 where price> 100"; st. addBatch (SQL); int [] a = st.exe cuteBatch (); for (int r: a) {System. out. println (r); // r indicates the number of records affected by each SQL statement }}

Preprocessing

 

 

Public void preparedBatch () throws Exception {Connection con = ConnFactory. getConn (); String SQL = "insert into book (name, price, pub) values (?,?,?) "; PreparedStatement pst = con. prepareStatement (SQL); for (int I = 0; I <5; I ++) {pst. setString (1, "Java" + I); pst. setDouble (2, 55.85 + I); pst. setString (3, "07:07:08"); pst. addBatch (); // The pst method cannot contain the parameter SQL} // pst.exe cuteBatch (); int [] a = pst.exe cuteBatch (); for (int r:) {System. out. println (r); // r indicates the number of records affected by each SQL statement }}

Note: The pre-processing method does not contain the parameter SQL, which is common.