Using software: mysql, eclipse

Link steps:

1. Registration driver

2. Create a connection object

3. Write sql statement

4. Execute sql statement and return a result or result set

5. Close the three connection objects (connection, statement and setresult). The closing order is (setresult -- > statement -- > setresult)

I. direct connection method: (this method is to write all the steps of sql statement and result together) this method is not recommended

 1 public static void main(String[] args) {
 2         String url = "jdbc:mysql://localhost:3306/students";
 3         String user = "root";
 4         String password = "admin";
 5         Connection conn = null;
 6         Statement st = null;
 7         
 8         try {
 9             // 1. Registration driven
10             Class.forName("com.mysql.jdbc.Driver");
11             // 2. Create a linked object
12             conn = DriverManager.getConnection(url,user,password);
13             // 3. Create a sql Send command object of statement
14             String sql = "insert into student values('2001','Tom','20','7000')";        
15             st= conn.createStatement();
16             // 4. implement sql Statement, get the result set object of the query
17             st.executeQuery(sql);20         } catch (Exception e) {
21             e.printStackTrace();
22         }finally {
23             // 5. Close links, command objects, result sets
24             if(st != null) {
25                 try {
26                     st.close();
27                 } catch (Exception e) {
28                     e.printStackTrace();
29                 }
30             }
31             if(conn != null) {
32                 try {
33                     conn.close();
34                 } catch (Exception e) {
35                     e.printStackTrace();
36                 }
37             }
38         }

2. Create tool class methods, write a class in necessary steps, and call directly when using (recommended)

1. Register driver, create connection object and close resource. Generally, these three parts can write a class. Because the results of writing sql statement and executing sql statement are inconsistent, they can be written when they are used

2. Generally, write tool classes are written as static methods to facilitate calling

//This is the tool class:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcUtils {
	private static String driverName = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://localhost:3306/student_achievement_system";
	private static String user = "root";
	private static String password = "admin";
	/**
	 * Linked database
	 */
	static {
		try {
			Class.forName(JdbcUtils.driverName);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * Get link object connection
	 * @return
	 */
	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(JdbcUtils.url, JdbcUtils.user, JdbcUtils.password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * close resource
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public static void close(Connection conn,Statement st,ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if(st != null) {
			try {
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

//This is the basic operation class for database

// Add, delete, update, find one, find all

public class StudentsDaoImpl implements IStudentsDao {
　　　　//increase
	@Override
	public int save(Students student) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "insert into students values(?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, student.getStudentId());
			ps.setString(2, student.getStudentName());
			ps.setString(3, student.getSex());
			ps.setString(4, student.getPhoneNo());
			ps.setString(5, student.getAddress());
			ps.setDate(6, (Date) student.getBirthday());
			int row = ps.executeUpdate();
			return row;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(conn, ps, null);
		}
		return 0;
	}
　　　　　　//delete
	@Override
	public int delete(int studentId) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "delete from students where studentId=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, studentId);
			int row = ps.executeUpdate();
			return row;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(conn, ps, null);
		}
		return 0;
	}
　　　　//To update
	@Override
	public int update(int studentId, Students student) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "update students set studentName=?,sex=?,phoneNo=?,address=?,birthday=? where studentId=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, student.getStudentName());
			ps.setString(2, student.getSex());
			ps.setString(3, student.getPhoneNo());
			ps.setString(4, student.getAddress());
			ps.setDate(5, ((Date) student.getBirthday()));
			ps.setInt(6, studentId);
			int row = ps.executeUpdate();
			return row;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(conn, ps, null);
		}
		return 0;
	}
　　　　　　//Find a piece of data
	@Override
	public Students getByStudentId(int studentId) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from students where studentId=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, studentId);
			rs = ps.executeQuery();
			if(rs.next()) {
				Students student = new Students();
				student.setStudentId(rs.getInt("studentId"));
				student.setStudentName(rs.getString("studentName"));
				student.setSex(rs.getString("sex"));
				student.setPhoneNo(rs.getString("phoneNo"));
				student.setAddress(rs.getString("address"));
				student.setBirthday(rs.getDate("birthday"));
				return student;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return null;
	}
　　　　　　//Find all data
	@Override
	public List<Students> getAll() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from students";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			List<Students> studentsList = new ArrayList<>();
			while(rs.next()) {
				Students student = new Students();
				student.setStudentId(rs.getInt("studentId"));
				student.setStudentName(rs.getString("studentName"));
				student.setSex(rs.getString("sex"));
				student.setPhoneNo(rs.getString("phoneNo"));
				student.setAddress(rs.getString("address"));
				student.setBirthday(rs.getDate("birthday"));
				studentsList.add(student);
			}
			return studentsList;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return null;
	}
}