1 database driver
Driver: sound card, graphics card, database
Our program will deal with the database through the database driver;
2 database driver
In order to simplify the operation of developers (unified database), sun company provides a specification (java database operation) that is JDBC. The implementation of these specifications is done by specific manufacturers
For developers, we only need to master the operation of jdbc
jar packages required for development: you can download them from maven's official website
java.sql
javax.sql
You also need to import the database driver package
3. The first jdbc program
1. Create a maven project
Create the database first
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci; USE `jdbcStudy`; CREATE TABLE `users`( `id` INT PRIMARY KEY, `NAME` VARCHAR(40), `PASSWORD` VARCHAR(40), `email` VARCHAR(60), birthday DATE ); INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(1,'zhangsan',123456,'zs@sina.com','1980-12-04'), (2,'lisi',123456,'lisi@sina.com','1981-12-04'), (3,'wangwu',123456,'wangwu@sina.com','1979-12-04')
Import database driver
The code is as follows:
package com.chen; import java.sql.*; //My first jdbc program public class test01 { public static void main(String[] args) throws ClassNotFoundException, SQLException { // 1 load drive Class.forName("com.mysql.jdbc.Driver"); // 2 user information and url String url="jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; String username="root"; String password="123456"; // 3. The link is successful Connection connection = DriverManager.getConnection(url, username, password); // 4. Objects executing sql Statement statement = connection.createStatement(); // 5. The object executing sql executes sql and returns the result if there is a result String sql=" SELECT * FROM users"; ResultSet resultSet = statement.executeQuery(sql); while(resultSet.next()){ System.out.println("id="+resultSet.getObject("id")); System.out.println("name="+resultSet.getObject("NAME")); System.out.println("passwd="+resultSet.getObject("PASSWORD")); System.out.println("email="+resultSet.getObject("email")); System.out.println("birthday="+resultSet.getObject("birthday")); } // 6 release the connection resultSet.close(); statement.close(); connection.close(); } }
Execution results:
Steps:
- Load driver
- Connect to database DriverManager
- Gets the Statement object that executes SQL
- Get return result set
- Release connection
// DRIVER Class.forName("com.mysql.jdbc.Driver");
// URL
String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
mysql default port 3306.
// statement
Write SQL: String sql=" SELECT * FROM users";
statement.executeQuery() -- query operation, return resultset statement.executeUpdate() -- modify, delete and add operations, and return the number of rows executed statement.execute() -- all sql statements can be executed, which is inefficient statement.executeBatch() -- multiple sql statements can be executed
// resultSet: encapsulates all query results
Returns data of the specified type
-
resultSet.getObject() resultSet.getDate()resultSet.getInt() resultSet.getString()........
resultSet.beforeFirst(); -- Move to the front resultSet.next(); -- Move to next resultSet.afterLast(); -- Move to the back
//Release resources
resultSet.close(); statement.close(); connection.close();
4 statement object
The statement object in java is used to send sql statements to the database. To complete the addition, deletion, modification and query of the database, you only need to provide this object to send sql statements to the database.
Query operation: returns query results
ResultSet resultSet = statement.executeQuery("select * from users"); System.out.println("id="+resultSet.getObject("id"));
Add: returns the number of affected rows
int i = statement.executeUpdate("insert into users(id,NAME,PASSWORD) values(1,'cxl','123456')"); if(i>0) System.out.println("Increase success");
Delete: returns the number of affected rows
int i = statement.executeUpdate("delete from users where id = 1"); if(i>0) System.out.println("Delete succeeded");
Modify: returns the number of affected rows
int i =statement.executeUpdate("update users set NAME='cxl' where id=1"); if(i>0) System.out.println("Modified successfully");
The use of tool classes can reduce the coupling of programs, facilitate development and improve performance. The code is as follows:
Tools:
package com.jdbc.util; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class jdbcutil { private static String driver=null; private static String url=null; private static String username=null; private static String password=null; static { // Get profile InputStream in = jdbcutil.class.getClassLoader().getResourceAsStream("connect.properties"); Properties properties = new Properties(); try { // Load profile properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); // Load driver Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } // Get connection public static Connection getConnect() throws SQLException { return DriverManager.getConnection(url, username, password); } // Release connection public static void res(Connection connection,Statement statement, ResultSet resultSet){ if(resultSet!=null){ try { resultSet.close(); } catch (SQLException throwables) { throwables.printStackTrace(); }} if(statement!=null){ try { statement.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
Insert:
package com.jdbc; import com.jdbc.util.jdbcutil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class insert { public static void main(String[] args) { Connection connect=null; Statement statement=null; ResultSet resultSet=null; // Get connection try { connect = jdbcutil.getConnect(); statement = connect.createStatement(); int i = statement.executeUpdate("INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" + "VALUES(4,'zhangsan2',123456,'zs2@sina.com','1980-12-04')"); while(i>0){ System.out.println("Insert successful"); break; } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { jdbcutil.res( connect,statement,resultSet); } } }
Delete:
package com.jdbc; import com.jdbc.util.jdbcutil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class delete { public static void main(String[] args) { Connection connect=null; Statement statement=null; ResultSet resultSet=null; try { connect = jdbcutil.getConnect(); statement = connect.createStatement(); int i = statement.executeUpdate("delete from users where id = 4"); while(i>0){ System.out.println("Delete succeeded"); break; } } catch (SQLException throwables) { throwables.printStackTrace(); } //Release connection finally { jdbcutil.res( connect,statement,resultSet); } } }
Modification:
package com.jdbc; import com.jdbc.util.jdbcutil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class update { public static void main(String[] args) { Connection connect =null; Statement statement=null; ResultSet resultSet=null; try { connect = jdbcutil.getConnect(); statement = connect.createStatement(); int i=statement.executeUpdate("UPDATE users SET `NAME`='chen',PASSWORD='12121',email='cxl@qq.com',birthday='1999-12-11' WHERE id = 1"); while (i>0){ System.out.println("Modified successfully"); break; } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { jdbcutil.res( connect,statement,resultSet); } } }
Query:
package com.jdbc; import com.jdbc.util.jdbcutil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class query { public static void main(String[] args) { Connection connect =null; Statement statement=null; ResultSet resultSet=null; try { connect = jdbcutil.getConnect(); statement= connect.createStatement(); resultSet = statement.executeQuery("select * from users where id = 1"); while(resultSet.next()){ System.out.println(resultSet.getObject("NAME")); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { jdbcutil.res( connect,statement,resultSet); } } }
Problems with sql injection:
There is a vulnerability in sql, which will be attacked and lead to data disclosure. (data will be spliced or)
For example, enter the code under the user name and password status normally, and output the result normally
package com.jdbc; import com.jdbc.util.jdbcutil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class sql_injection { public static void main(String[] args) { login("lisi","123456"); } public static void login(String username,String password){ Connection connect =null; Statement statement=null; ResultSet resultSet=null; try { connect = jdbcutil.getConnect(); statement = connect.createStatement(); resultSet = statement.executeQuery("select * from users where `NAME`='" + username + "' and PASSWORD='" + password + "'"); while (resultSet.next()){ System.out.println(resultSet.getObject("birthday")); break; } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { jdbcutil.res(connect,statement,resultSet); } } }
Input under abnormal state: the user name and password are not entered correctly
package com.jdbc; import com.jdbc.util.jdbcutil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class sql_injection { public static void main(String[] args) { login("'or' 1=1","'or' 1=1"); } public static void login(String username,String password){ Connection connect =null; Statement statement=null; ResultSet resultSet=null; try { connect = jdbcutil.getConnect(); statement = connect.createStatement(); resultSet = statement.executeQuery("select * from users where `NAME`='" + username + "' and PASSWORD='" + password + "'"); while (resultSet.next()){ System.out.println(resultSet.getObject("birthday")); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { jdbcutil.res(connect,statement,resultSet); } } }
Result: all data in the table can be accessed illegally, which is the harm of sql injection
5 preparedstatement object
preparedstatement can prevent sql injection and is more efficient.
Use preparedstatement to solve the sql injection problem. The code is as follows:
package com.jdbc; import com.jdbc.util.jdbcutil; import java.sql.*; public class sql_injection { public static void main(String[] args) { // login("'or' 1=1","'or' 1=1"); login("lisi","123456"); } public static void login(String username,String password){ Connection connect =null; PreparedStatement statement=null; ResultSet resultSet=null; try { connect = jdbcutil.getConnect(); String sql="select * from users where `NAME`=? and PASSWORD=?"; statement = connect.prepareStatement(sql); statement.setString(1,username); statement.setString(2,password); resultSet = statement.executeQuery(); while (resultSet.next()){ System.out.println(resultSet.getObject("birthday")); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { jdbcutil.res(connect,statement,resultSet); } } /* public static void main(String[] args) { login("'or' 1=1","'or' 1=1"); } public static void login(String username,String password){ Connection connect =null; Statement statement=null; ResultSet resultSet=null; try { connect = jdbcutil.getConnect(); statement = connect.createStatement(); resultSet = statement.executeQuery("select * from users where `NAME`='" + username + "' and PASSWORD='" + password + "'"); while (resultSet.next()){ System.out.println(resultSet.getObject("birthday")); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { jdbcutil.res(connect,statement,resultSet); } }*/ }