Error message here!

Hide Error message here!

忘记密码?

Error message here!

请输入正确邮箱

Hide Error message here!

密码丢失?请输入您的电子邮件地址。您将收到一个重设密码链接。

Error message here!

返回登录

Close

Overview of JDBC in JDBC ~ Java, precautions for connecting to the database, and adding, deleting and modifying by using Preparedstatement

Salute-Y 2022-05-14 15:58:46 阅读数:1 评论数:0 点赞数:0 收藏数:0

JDBC summary

  • JDBC yes JAVA A set of API, Can be used to access different databases
  • This set of interfaces is sun Provided by the company , Different database manufacturers will provide different implementations for this set of interfaces , These implementations are the drivers of each database

step

 Insert picture description here

  • Must have objects :Connection、Statement
  • In the middle SQL sentence
  • Close the resource after use

Several ways to connect to the database

Information that must be given :

  • Which database driver to use
  • url
  • user name
  • password

Load registered driver

  • For loading driver Class Class forName Method , Put the drive type in
  • Register driver DriverManger Class registerDriver Method

Fill in URL

  • adopt URL Choose the right driver
  • The format is Protocol + Word agreement + Host name + Port number + Database name + Parameters , Such as jdbc:mysql://localhost:3306/db2

How to connect to the database
Mode one

 public static void testConnection1() throws SQLException {

Driver driver=new com.mysql.cj.jdbc.Driver();
String url="jdbc:mysql://localhost:3306/atguigudb1?serverTimezone=GMT%2B8";
Properties info=new Properties();
info.setProperty("user","root");
info.setProperty("password","llll");
Connection conn=driver.connect(url,info);
System.out.println(conn);
}

Mode two

 public static void testConnection2() throws Exception {

//1. obtain Driver Implementation class object , Use reflection to get 
Class clazz=Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver=(Driver) clazz.newInstance();
String url="jdbc:mysql://localhost:3306/db2?serverTimezone=GMT%2B8";
Properties info=new Properties();
info.setProperty("user","root");
info.setProperty("password","llll");
Connection conn=driver.connect(url,info);
System.out.println(conn);
}

Mode three

 public static void testConnection3() throws Exception {

Class clazz=Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver=(Driver) clazz.newInstance();
String url="jdbc:mysql://localhost:3306/db2?serverTimezone=GMT%2B8";
String user="root";
String password="llll";
// Registration drive 
DriverManager.registerDriver(driver);
Connection conn=DriverManager.getConnection(url,user,password);
System.out.println(conn);
}

Mode 4

 public static void testConnection4() throws Exception {

String url="jdbc:mysql://localhost:3306/db2?serverTimezone=GMT%2B8";
String user="root";
String password="llll";
//2. load Driver No explicit registration driver In fact, the loading process can also be omitted 
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con=DriverManager.getConnection(url,user,password);
System.out.println(con);
}

Methods five

 // Put the information of connecting to the database into the configuration file , Connect directly 
public static void testConnection5() throws Exception {

//1. Read the basic information in the configuration file 
InputStream is=ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros=new Properties();
pros.load(is);
String user=pros.getProperty("user");
String password=pros.getProperty("password");
String url=pros.getProperty("url");
String driverClass=pros.getProperty("driverClass");
Class.forName(driverClass);
Connection con=DriverManager.getConnection(url,user,password);
System.out.println(con);
}

utilize JDBC Operating the database

  • For addition, deletion, modification and query, you must use Statement
  • however Statement There are drawbacks ( It needs to be concatenated + There will be SQL Injection problem )
  • So there's no need to , use PreparedStatement

PreparedStatement

  • adopt Connection Object call preparedStatement Method , hold SQL The statement is written in the method parameter

 Insert picture description here

Statement The function of is equivalent to that of a courier , What we're going to do is SQL Write to it , It's going to SQL Pass it to the specific database area for execution

utilize JDBC Implementation add (insert)

 public static void testInsert() throws IOException, ClassNotFoundException, SQLException {

//1. Read... In the configuration file 4 A basic message 
InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros=new Properties();
pros.load(is);
String user=pros.getProperty("user");
String password=pros.getProperty("password");
String url=pros.getProperty("url");
String driverClass=pros.getProperty("driverClass");
//2. The load driver 
Class.forName(driverClass);
//3. For a link 
Connection con=DriverManager.getConnection(url,user,password);
//4. precompile Sql sentence , return PreparedStatement Example 
String sql="insert into account(id,name,balance)values(?,?,?)";
PreparedStatement ps=con.prepareStatement(sql);
//5. Fill in placeholders 
ps.setInt(1,6);
ps.setString(2," Zhu Xiaoming ");
ps.setDouble(3,50.00);
//6. Perform the operation 
ps.execute();
//7. Closure of resources 
ps.close();
}

utilize JDBC Implementation delete (delete)

public static void testDelete() throws Exception{

//1. For a link 
Connection connection=JDBCUtils.getConnection();
//2. precompile sql sentence 
String sql="delete from account where id=?";
PreparedStatement statement=connection.prepareStatement(sql);
statement.setInt(1,2);
//3. perform 
statement.execute();
//4. Release resources 
JDBCUtils.closeResource(connection,statement);
}

Package the same code , Implement modification (update)

We found that with JDBC When operating the database ( Additions and deletions ), All have the same part of the code : Get database connection 、 close resource .
You can wrap these two parts of code in a class , Call directly , Simplify the code

encapsulation

public class JDBCUtils {

public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {

InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros=new Properties();
pros.load(is);
String user=pros.getProperty("user");
String password=pros.getProperty("password");
String url=pros.getProperty("url");
String driverClass=pros.getProperty("driverClass");
//2. The load driver 
Class.forName(driverClass);
//3. For a link 
Connection con= DriverManager.getConnection(url,user,password);
return con;
}
public static void closeResource(Connection conn, Statement ps) throws SQLException {

ps.close();
conn.close();
}
}

modify

 public static void testUpdate() throws SQLException, IOException, ClassNotFoundException {

//1. For a link 
Connection connection= JDBCUtils.getConnection();
//2. precompile sql sentence , return PreparedStatement Example 
String sql="update account set name=? where id=?";
PreparedStatement statement=connection.prepareStatement(sql);
statement.setString(1," Cold lemon ");
statement.setObject(2,6);
// perform 
statement.execute();
//5. close resource 
JDBCUtils.closeResource(connection,statement);
}

utilize JDBC Implement a method to add, delete and modify

 public static void main(String[] args) throws Exception {

String sql="delete from account where id=?";
update(sql,6);
}
public static void update(String sql,Object...args)throws Exception{

//1. Get the connection to the database 
Connection connection=JDBCUtils.getConnection();
//2. precompile sql sentence , return PreparedStatement Example 
PreparedStatement statement=connection.prepareStatement(sql);
//3. Fill in placeholders 
for(int i=0;i<args.length;i++){

statement.setObject(i+1,args[i]);
}
//4. perform 
statement.execute();
//5. close resource 
JDBCUtils.closeResource(connection,statement);
}
Copyright statement
In this paper,the author:[Salute-Y],Reprint please bring the original link, thank you