1.JDBC introduction
jdbc(java database connectivity) provides a unified programming interface for java developers to use the database. It consists of a set of java classes and interfaces.
The classes and interfaces needed for JDBC are:
DriverManager,Connection,Statement,ResultSet
2. Download MySQL connector Java
The mysql version of this machine is 5.7.26 win32, so this chapter uses this version as an example to access mysql:
Then enter https://dev.mysql.com/downloads/connector/j/ Download mysql-connector-java.jar package to connect mysql
As shown in the figure below, only version 8.0.19 is available, so we can download it. No matter 64 bit or 32-bit, it can be accessed
After downloading and decompressing, there is a mysql-connector-java-8.0.19.jar:
Next, we will test whether we can access
3.JDBC use process
3.1 register driver through DriverManager. registerDriver(Driver driver)
Note that when using the new Driver, you need to select com.mysql.cj.jdbc.Driver:
Because com.mysql.jdbc.Driver has been discarded
PS: you can also directly change DriverManager. registerDriver(Driver driver) to:
Class.forName("com.mysql.cj.jdbc.Driver"); //Load this class to register the driver,because mysql Driver Class already called in static code block registerDriver()To register drivers.
3.2 then connect to the database through Connection DriverManager.getConnection(String url, String user, String password) and get the Connection object
url: fill in "jdbc: sub protocol: / / ip address: port number / database name", if mysql, fill in "jdbc:mysql://localhost:3306 / database name"
For mysql-connector-java-8.0 and above, add "? Characterecoding = UTF-8 & usessl = false & servertimezone = UTC & rewritebatchedstatements = true"
username: mysql user name
Password: mysql password
3.3 get the statement object through the Connection object
Statement statement = connection.createStatement();
3.4 execute query sql statement through executeQuery(String) of statement object and return ResultSet database result set
For example:
ResultSet resultSet = statement. executeQuery("select * from student"); //Obtain student Data in the table
In addition, the int executeUpdate(String sql) method is also used to implement INSERT, UPDATE or DELETE statements. The return value represents the number of data rows affected after the execution of the sql statement (described in the following examples)
3.5 then read the query content through the ResultSet
The common methods of ResultSet are as follows:
boolean first(); //Move to the first row of content data boolean last(); //Move to the last row of content data int getRow() ; //Get the line number of the current cursor boolean isLast() //Gets whether the cursor is located here ResultSet The last line of the object. boolean next(); //Move to next row of data,Then you can go through getXXX()After obtaining the current row of data,Pass through next()To move down to continue getXXX(),Until next()Return to false Until boolean previous(); //Move to previous row of data String getString(String columnLabel); //Get the columnLabel Contents of column names String getString(int columnIndex); //Get the columnIndex Column contents,The first column starts at 1. String getInt (String columnLabel); //Get the columnLabel Contents of column names String getInt(int columnIndex); //Get the columnIndex Column contents,The first column starts at 1. //...Besides,Also getFloat(),getLong(),getShort(),getURL(),getBoolean(),getRowId()
PS: after getting the ResultSet, you must next() once to get xxx() to get the content
3.6 after the access, release MySQL resources (after all, the number of MySQL connections is limited)
try { if(resultSet!=null){ resultSet.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if(statement!=null){ statement.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if(connection!=null){ connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
4. For the database to be accessed in this chapter, take students as an example:
5. Write the JdbcUtils tool class first
In the JdbcUtils tool class, getconnection() and releaseresc() are mainly written, so as to avoid subsequent duplicate code generation
The code of JdbcUtils.java is as follows:
public class JdbcUtils { private static String driver; private static String url; private static String user; private static String pwd; static{ driver = "com.mysql.cj.jdbc.Driver"; url = "jdbc:mysql://localhost:3306/students?characterEcoding=utf-8&" + "useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true"; user = "root"; pwd = "sql"; } //Get a link mysql Of Connection object static public Connection getConnection(){ try { Class.forName(driver); Connection connection = DriverManager.getConnection(url,user,pwd); return connection; } catch (Exception e) { e.printStackTrace(); return null; } } /** * Release MySQL resources (after all, the number of MySQL connections is limited) * @param resultSet Result set * @param statement * @param connection link */ public static void releaseResc(ResultSet resultSet, Statement statement, Connection connection) { try { if(resultSet!=null){ resultSet.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if(statement!=null){ statement.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if(connection!=null){ connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
6. Database query example
Query all students:
@Test public void jdbcQuery(){ ResultSet resultSet = null; Statement statement = null; Connection connection = null; try { connection = JdbcUtils.getConnection(); //adopt JdbcUtils Obtain connection statement = connection.createStatement(); String sql = "select * from student"; resultSet = statement.executeQuery(sql); while(resultSet.next()){ String name = resultSet.getString("name"); String score = resultSet.getString("score"); String classs = resultSet.getString("class"); System.out.println("Full name:"+name+" achievement:"+score+" class:"+classs); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JdbcUtils.releaseResc(resultSet, statement, connection); //Release resources } }
Print as follows:
7. Database insertion example
@Test public void jdbcInser(){ ResultSet resultSet = null; Statement statement = null; Connection connection = null; try { connection = JdbcUtils.getConnection(); statement = connection.createStatement(); String name = "Small f"; int score = 99; String classs = "First 2-4 class"; String sql = "INSERT INTO student(name,score,class) " +" values('"+name+"','"+String.valueOf(score)+"','"+classs+"')"; int result = statement.executeUpdate(sql); //executeUpdate:Used to achieve INSERT,UPDATE or DELETE Sentence,Return value indicates execution sql Number of data rows affected after statement System.out.println("Inserted"+result+"Bar data"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JdbcUtils.releaseResc(resultSet, statement, connection); //Release resources } }
Run print:
View database:
8. Database update example
Change the scores of all students below 60 to 0:
@Test public void jdbcUpdate(){ ResultSet resultSet = null; Statement statement = null; Connection connection = null; try { connection = JdbcUtils.getConnection(); statement = connection.createStatement(); String sql = "update student SET score='0' WHERE score<60"; int result = statement.executeUpdate(sql); //executeUpdate:Used to achieve INSERT,UPDATE or DELETE Sentence,Return value indicates execution sql Number of data rows affected after statement System.out.println("Updated"+result+"Bar data"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JdbcUtils.releaseResc(resultSet, statement, connection); //Release resources } }
View database:
9.SQL injection attack
Statement adopts the way of compiling SQL statement directly and throwing it to database for execution, so it is easy to be attacked by SQL injection
For example:
When we log in for execution, we need to execute:
statement.executeQuery("select id from users where name ='"+username+"' and password = '"+password+"'");
The hacker changes the string directly to:
statement.executeQuery("select id from users where name ='"+username+"' or '1==1' and password = '"+password+"'");
You can enter passwords without permission and log in, so another class PreparedStatement is provided in java. It uses the "? Placeholder to precompile and then fill in parameters to avoid SQL injection attacks
Introduction to PreparedStatement class
Use the "? Placeholder to precompile, then fill in the parameters, and then fill in the parameters through setXXX(). For example, setString():
setString(int parameterIndex, String x); //Xiang di parameterIndex Placeholders to fill in x content // parameterIndex:First?Placeholder is 1,The second is 2.... //...In addition to this method,Also setFloat(),setLong(),setBoolean()....Wait
Modify the PreparedStatement of the login interface as follows:
public static boolean login(String username,String password){ Connection connection = JdbcUtils.getConnection(); //Obtain try { String sql = "select id from users where username =? and password = ?"; //To run sql Sentence,adopt?To replace the login account and password PreparedStatement preparedStatement = connection.prepareStatement(sql); //First? use username String de substitution preparedStatement.setString(1, username); //The second? use password String de substitution preparedStatement.setString(2, password); ResultSet resultSet = preparedStatement.executeQuery(); return resultSet.next(); //Return if there is a value true,Otherwise return false; } catch (SQLException e) { e.printStackTrace(); return false; } }