JDBC introduction
-
JDBC(Java Database Connectivity) is a common interface (a set of API s) independent of a specific database management system and common SQL database access and operation. It defines the standard Java class libraries used to access the database (java.sql, javax.sql). Using these class libraries, you can easily access database resources in a standard way
-
JDBC provides a unified way to access different databases and shields some details for developers
1.JDBC programming steps
2. Get database connection
1.1 element 1: Driver interface implementation class (Driver class)
The java.sql.Driver interface is the interface that all JDBC drivers need to implement. mySql driver: com.mysql.cj.jdbc.Driver
Copy the driver jar package to a directory of the Java project. It is customary to create a new lib folder. (copy the file and paste it in bin)
Right click the driver jar – > build path – > add to build path
(this driver package can only be used after creating a new path, otherwise it is unavailable)
1.2 loading and registering JDBC drivers
Load driver: to load the JDBC driver, you need to call the static method forName() of Class class and pass the Class name of the JDBC driver to be loaded
Class.forName(“com.mysql.cj.jdbc.Driver”); Register driver: DriverManager class is the driver manager class, which is responsible for managing drivers
Use DriverManager.registerDriver ("com.mysql.cj.jdbc.Driver") to register drivers
2. Element 2: URL (uniform resource locator, i.e. website)
JDBC URL is used to identify a registered driver. The driver manager selects the correct driver through this URL to establish a connection to the database.
jdbc: sub protocol: sub name protocol: Protocol in JDBC URL: jdbc sub protocol: sub protocol is used to identify a database driver. Sub Name: a method to identify a database.
MySQL connection URL writing method:
jdbc:mysql://Host name: mysql service port number / database name? Parameter = value & parameter = value jdbc:mysql://localhost:3306/atguigu jdbc:mysql://127.0.0.1\:3306/librarymanage
3 element 3: user name and password
User and password can tell the database that the getConnection() method of DriverManager class can be called to establish a connection to the database in the way of "attribute name = attribute value"
user=root password=xxxxxxxxx
Connection mode 1: basic principle (not commonly used)
//1. Provide the object of java.sql.Driver interface implementation class to realize driver connection
Driver driver = new com.mysql.cj.jdbc.Driver();
//2. Provide url to operate a database
String url = "jdbc:mysql://localhost:3306/test";
//3. Provide the Properties object, indicating the user name root and password
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "XXXXXX");
//4. Call the connect() of the driver to get the connection and connect to the database
Connection conn = driver.connect(url, info);The properties class is mainly used to read Java configuration files. Different programming languages have their own supported configuration files. Many variables in the configuration file are often changed. In order to facilitate users' configuration, users can modify relevant variable settings without the program itself. Just like in Java, its configuration file is often a. Properties file, which configures parameters in the form of key value pairs.
Connection method 2: (common)
//1. Four basic elements of database connection: String url = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "XXXXXX"; String driverName = "com.mysql.jdbc.Driver"; //2. Load Driver (① instantiate Driver ② register Driver) Class.forName(driverName); //3. Register driver DriverManager.registerDriver(driver); //4. Get connection Connection conn = DriverManager.getConnection(url, user, password);
3. Add, delete, modify and query the database
The addition, deletion, modification and query of database are realized by sql statements, and the addition, deletion and modification can be classified into one category. The operations are the same, but the sql is different, and the query is another category. These operations are implemented by the PrepatedStatement.
PreparedStatement introduction
-
The PreparedStatement object can be obtained by calling the preparedStatement(String sql) method of the Connection object
-
PreparedStatement interface is a sub interface of Statement, which represents a precompiled SQL Statement
-
The parameters in the SQL statement represented by the PreparedStatement object are represented by a question mark (?). Call the setXxx() method of the PreparedStatement object to set these parameters
-
The setXxx() method has two parameters. The first parameter is the index of the parameter in the SQL statement to be set (starting from 1), and the second parameter is the value of the parameter in the SQL statement to be set
//add to insert into Table name(variable)values(?) //modify update Table name set variable= ? where Variables for positioning = ? //delete delete form Table name where Variables for positioning = ?; //query select variable from Table name where Variables for positioning = ?
For example:
//Add a record to the database
public void testconnction() {
Connection conn=null;
PreparedStatement ps=null;
try {
//Read the three basic information of the configuration file
String url="jdbc:mysql://localhost:3306/manage";
String user="root";
String passsword="XXXXX";
//Load driver
Class.forName("com.mysql.cj.jdbc.Driver");
//Get connection
conn=DriverManager.getConnection(url,user,passsword);
//Precompiled sql statement and returned ps instance
String sql
= "insert into student(id,name,age)values(?,?,?)";
ps = conn.prepareStatement(sql);
//Fill placeholder
ps.setInt(1,4);(1:For location)
ps.setString(2,"nezha");
ps.setInt(3,19);
//Perform operation
ps.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
//Resource shutdown
try {
if(ps!=null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
After operation:
//Modify a record in the database
public void testUpate() {
Connection conn=null;
PreparedStatement ps=null;
try {
//1. Database connection
//Read the three basic information of the configuration file
String url="jdbc:mysql://localhost:3306/manage";
String user="root";
String passsword="XXXXX";
//Load driver
Class.forName("com.mysql.cj.jdbc.Driver");
//Get connection
conn=DriverManager.getConnection(url,user,passsword);
//2. Precompile the sql statement and return the instance of preparedstatement
String sql = "update student set name= ? where id = ?";//Modify the name and find the location by id
ps = conn.prepareStatement(sql);
//3. Fill placeholder
ps.setObject(1, "Xiaoshuai");
ps.setObject(2, 1);
//4. Implementation
ps.execute();
}
catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5. Closure of resources
try {
if(ps!=null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
} }
After operation:
//Delete a record
public void testconnction() {
Connection conn=null;
PreparedStatement ps=null;
try {
//Read the three basic information of the configuration file
String url="jdbc:mysql://localhost:3306/manage
String user="root";
String passsword="XXXXXX";
//Load driver
Class.forName("com.mysql.cj.jdbc.Driver");
//Get connection
conn = DriverManager.getConnection(url,user,passsword);
// System.out.println(conn);
//Precompiled sql statement and returned ps instance
String sql = "DELETE FROM student WHERE id = ?";
ps = conn.prepareStatement(sql);
//Fill placeholder
ps.setInt(1,1);
//Perform operation
ps.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
//Resource shutdown
try {
if(ps!=null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}//General addition, deletion and modification operations
public void update(String sql,Object ...args) {//The number of placeholders in sql is consistent with the number of variable parameter args
Connection conn=null;
PreparedStatement ps=null;
try {
//1. Get database connection
conn = jdbcUtil.getConnection();
//2. Precompile the sql statement and return the instance of preparestament
ps = conn.prepareStatement(sql);
//3. Fill placeholder
for(int i=0;i<args.length;i++)
{
ps.setObject(i+1, args[i]);//Beware of parameter declaration errors
}
//4. Implementation
ps.execute();
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5. Closure of resources
jdbcUtil.closeResource(conn, ps);
}
}Call common code:
public void testCommonupdate() {
Scanner input = new Scanner(System.in);
System.out.println("Please enter the title of the book");
String book_name=input.next();
System.out.println("Please enter price");
double book_price=input.nextDouble();
System.out.println("Please enter author");
String book_writer=input.next();
System.out.println("Please enter quantity");
int book_num=input.nextInt();
String sql="insert into book(book_name,book_price,book_writer,book_num)values(?,?,?,?)";
update(sql,book_name,book_price,book_writer,book_num);
}