# JDBC Introduction Foundation, Use Steps
Recently, in reviewing the knowledge of JDBC, first write a review of the basis of JDBC.
For the understanding of JDBC, the full name of JDBC is Java Database Connectvity, so it is not difficult to write out, that is, using the java language to operate the database. JDBC actually defines an interface to operate different relational databases (MySQL, Oracle, DB2). java code can operate these relational databases through JDBC interface. Each database also provides its own driver jar packages. Using the driver jar packages of different databases, different databases can operate different databases through JDBC.
## Basic usage steps for introducing JDBC
1. Import drive jar package to project
- Copy mysql-connector-java-5.0.8-bin.jar to the libs directory
- Right-click -> Add As Library
II. Registration Driver
Class.ForName("com.mysql.jdbc.Driver");
3. Getting Connection of Database Connection Objects
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db","user","password")
//If it's local
//Connection conn = DriverManager.getConnection("jdbc:mysql:///db","user","password")
//db is the database name, user is the user, password is the password
Definition of sql
String sql = "select *from student";//sql statement
5. Get the object Statement that executes sql
Statement stmt = conn.CreateStatement();
6. Execution of sql
ResultSet rs = stmt.executeQuery(sql); //query //int count= stmt.executeUptate(sql); // The number of rows returned is affected
VII. Processing results
while (rs.next())
{
//get data
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
}
//system.out.printl(count);
VIII. Releasing Resources
rs.close();//First Release of Final Application stmt.close(); conn.close();
## Operational cases
Take the operation student table as an example
1. Create a new domain package under the src directory, create stu classes, and encapsulate JavaBean s corresponding to stu tables
package domain;
//JavaBean encapsulating corresponding stu tables
public class stu {
private int id;
private String name;
private int age;
private String sex;
private int math;
private int english;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getMath() {
return math;
}
public void setMath(int math) {
this.math = math;
}
public int getEnglish() {
return english;
}
public void setEnglish(int english) {
this.english = english;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "stu{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
", math=" + math +
", english=" + english +
", address='" + address + '\'' +
'}';
}
}
2. Establish a configuration file, record the information of the database connection object, operate different databases to modify the configuration file directly, and avoid modifying the code. Create configuration files in IDEA:
Right-click //new//Resource Bundle//jdbc.properties in the src directory
url = jdbc:mysql:///db1
user = root
password = root
driver = com.mysql.jdbc.Driver
3. Extracting tool classes to avoid a lot of duplicate code
Create a new package util under src to create a tool class like JDBCUtils
Register Driver, Get Connection Objects, Release Resources and Write them directly into Tool Class
package util;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* jdbc Tool class
*/
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
/**
* File reading, only need to read once to get these values. Using static code blocks
*/
static {
//Read file resources
try {
//1. Create properties collection classes
Properties pro = new Properties();
//ClassLoader Class Loader Class Loader Class Loader Class Loader
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
System.out.println(path);
//2. Loading files
//pro.load(new FileReader("src/jdbc.properties"));
pro.load(new FileReader(path));
//3. Access to data
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
//4. Registration Driver
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//Get the tool method of the connection and return the object of the connection
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
/**
* Release resources
* @param stmt
* @param conn
*/
public static void close(Statement stmt,Connection conn){
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Release rs by overloaded close method
* @param rs
* @param stmt
* @param conn
*/
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
4. Build another package jdbc and create a new class JdbcDemo to demonstrate the process.
package jdbc;
import domain.stu;
import util.JDBCUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcDemo {
public static void main(String[] args) {
List list = new JdbcDemo().FindAll();
System.out.println(list);
}
/**
* Testing jdbc tool class
*/
public List<stu> FindAll(){
//Jdbc Query Steps
Connection conn = null;
Statement stmt = null;
ResultSet rs =null;
List<stu> list = null;
try {
/* 1,Registration driven
2,Get the connection object
Direct invocation of tool classes
*/
conn = JDBCUtils.getConnection();
//3, write sql
String sql = "select *from student";
//4. Get the object that executes sql
stmt = conn.createStatement();
//5. Executing sql
rs = stmt.executeQuery(sql);
//6. Traversing result sets, encapsulating objects, loading sets
stu stu = null;
list = new ArrayList<stu>();
while (rs.next())
{
//get data
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String sex = rs.getString("sex");
int math = rs.getInt("math");
int english = rs.getInt("english");
String address = rs.getString("address");
//Create stu objects and assign values
stu = new stu();
stu.setId(id);
stu.setName(name);
stu.setAge(age);
stu.setSex(sex);
stu.setMath(math);
stu.setEnglish(english);
stu.setAddress(address);
//Loading set
list.add(stu);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
/*
Release resources and call the close() method of the tool class directly
*/
JDBCUtils.close(rs,stmt,conn);
}
return list;
}
}
So far, the basic JDBC usage demonstration case has been completed.