看完这篇文章，你也可以手写MyBatis部分源码（JDBC）

Connection conn = DriverManager.getConnection(url,username,password);
// url=jdbc:mysql://localhost:3306/jdbcdemo
// 如果连接的是本机的 MySQL,并且端口是默认的 3306 ,则可以简写:
//  url=jdbc:mysql:///jdbcdemo
// username:当前访问数据库的用户名
// password:当前访问数据库的密码
1.
2.
3.
4.
5.
6.

@Test
    public void testConnection4() {
        try {
            //1.数据库连接的4个基本要素：
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "admin";
            String driverName = "com.mysql.jdbc.Driver";

            //2.加载驱动 （①实例化Driver ②注册驱动）
            Class.forName(driverName);

            //3.获取连接
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

@Test
    public  void testConnection5() throws Exception {
    	//1.加载配置文件
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        
        //2.读取配置信息
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //3.加载驱动
        Class.forName(driverClass);

        //4.获取连接
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);

    }
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

cn.util;    //存放工具类
   cn.domain; //装pss模块的domain类,模型对象.(Student)
   cn.dao; //装pss模块的dao接口.调用者将需要保存的数据封装到一个对象中,然后传递进来
    cn.dao.impl; //装dao接口的实现类.
   cn.test; //暂时存储DAO的测试类,以后的测试类不应该放这里.
1.
2.
3.
4.
5.

int excuteUpdate(String sql)：执行更新操作INSERT、UPDATE、DELETE
ResultSet executeQuery(String sql)：执行查询操作SELECT
1.
2.

public class StatementTest {

	// 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
	@Test
	public void testLogin() {
		Scanner scan = new Scanner(System.in);

		System.out.print("用户名：");
		String userName = scan.nextLine();
		System.out.print("密   码：");
		String password = scan.nextLine();

		// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
		String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
				+ "'";//字符串拼接过于繁杂
		User user = get(sql, User.class);
		if (user != null) {
			System.out.println("登陆成功!");
		} else {
			System.out.println("用户名或密码错误！");
		}
	}

	// 使用Statement实现对数据表的查询操作
	public <T> T get(String sql, Class<T> clazz) {
		T t = null;

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 1.加载配置文件
			InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties pros = new Properties();
			pros.load(is);

			// 2.读取配置信息
			String user = pros.getProperty("user");
			String password = pros.getProperty("password");
			String url = pros.getProperty("url");
			String driverClass = pros.getProperty("driverClass");

			// 3.加载驱动
			Class.forName(driverClass);

			// 4.获取连接
			conn = DriverManager.getConnection(url, user, password);

			st = conn.createStatement();

			rs = st.executeQuery(sql);

			// 获取结果集的元数据
			ResultSetMetaData rsmd = rs.getMetaData();

			// 获取结果集的列数
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {

				t = clazz.newInstance();

				for (int i = 0; i < columnCount; i++) {
					// //1. 获取列的名称
					// String columnName = rsmd.getColumnName(i+1);

					// 1. 获取列的别名
					String columnName = rsmd.getColumnLabel(i + 1);

					// 2. 根据列名获取对应数据表中的数据
					Object columnVal = rs.getObject(columnName);

					// 3. 将数据表中得到的数据，封装进对象
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnVal);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return null;
	}
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.

// 常用方法:
void setXxx(int parameterIndex,Xxx value); //设置第几个占位符的真正参数值.
// Xxx 表示数据类型,比如 String,int,long,Date等.
void setObject(int parameterIndex, Object x); //设置第几个占位符的真正参数值.
int executeUpdate(); //执行DDL/DML语句. 注意:没有参数
// 若当前 SQL是 DDL语句,则返回 0.
// 若当前 SQL是 DML语句,则返回受影响的行数.
ResultSet executeQuery(); //执行DQL语句,返回结果集.
close(); //释放资源
1.
2.
3.
4.
5.
6.
7.
8.
9.

getInt(1), getString("name")
1.

package domain;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author Xiao_Lin
 * @date 2021/1/2 19:44
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
  //id
  private Integer id;
  //用户名
  private String username;
  //密码
  private String pwd;

  //构造方法的重载
  public User(String username, String pwd) {
    this.username = username;
    this.pwd = pwd;
  }
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

package dao.impl;

import dao.IUserDAO;

import dao.utils.DaoUtils;
import domain.User;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * @author Xiao_Lin
 * @date 2021/1/2 19:49
 */

public class UserDAOImpl implements IUserDAO {
  Connection connection = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  @Override
  public void insert(User user) {
    try {
      connection = DaoUtils.getConnection();
      String sql = "insert into user (username,pwd) values (?,?)";
       ps = connection.prepareStatement(sql);
      ps.setString(1,user.getUsername());
      ps.setString(2,user.getPwd());
      ps.executeUpdate();
      System.out.println("添加成功！！");
    } catch (SQLException e) {
      e.printStackTrace();
    }finally {
      DaoUtils.close(connection,ps,null);
    }
  }

  @Override
  public void delete(Integer id) {
    try {
      connection = DaoUtils.getConnection();
      ps = connection.prepareStatement("delete from user where id = ?");
      ps.setInt(1,id);
      ps.executeUpdate();
      System.out.println("删除成功！！");

    } catch (Exception e) {
      e.printStackTrace();
    }finally {
      DaoUtils.close(connection,ps,null);
    }
  }

  @Override
  public void update(User user) {
    try {
      connection = DaoUtils.getConnection();
       ps = connection.prepareStatement("update user set username = ? , pwd = ? where id = ?");
       ps.setString(1,user.getUsername());
       ps.setString(2,user.getPwd());
       ps.setInt(3,user.getId());
      ps.executeUpdate();
      System.out.println("修改成功");
    } catch (Exception e) {
      e.printStackTrace();
    }finally {
      DaoUtils.close(connection,ps,null);
    }

  }

  @Override
  public List<User> selectAll() {
    List<User> users = new ArrayList<>();
    try {
      connection = DaoUtils.getConnection();
      ps = connection.prepareStatement("select * from user");
      rs = ps.executeQuery();
      while (rs.next()){
        users.add(new User(rs.getInt("id"),rs.getString("username"),rs.getString("pwd")));
      }

    } catch (Exception e) {
      e.printStackTrace();
    }finally {
      DaoUtils.close(connection,ps,rs);
    }
    return users;
  }

  @Override
  public User selectUserById(Integer id) {
    User user = null;
    try {
      connection = DaoUtils.getConnection();
      ps = connection.prepareStatement("select * from user where id = ?");
      ps.setInt(1,id);
      rs = ps.executeQuery();
      while (rs.next()){
        user = new User(rs.getInt("id"),rs.getString("username"),rs.getString("pwd"));
      }

    } catch (Exception e) {
      e.printStackTrace();
    }finally {
      DaoUtils.close(connection,ps,rs);
    }
    return user;
  }
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.

package dao.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author Xiao_Lin
 * @date 2021/1/2 19:56
 */
public class DaoUtils {
  static Connection connection = null;
  static Properties properties = null;

  static {
    InputStream resourceAsStream = Thread.currentThread().getContextClassLoader()
        .getResourceAsStream("db.properties");
    properties = new Properties();
    try {
      properties.load(resourceAsStream);
      Class.forName(properties.getProperty("DriverClassName"));
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public static Connection getConnection() throws SQLException {
   connection= DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),properties.getProperty("password"));
    return connection;
  }

  public  static void  close(Connection conn , PreparedStatement ps , ResultSet rs){

    try {
      if(rs!=null){
        rs.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }finally{
      try {
        if(ps!=null){
          ps.close();
        }
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }finally{
        try {
          if(conn!=null){
            conn.close();
          }
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
    }
  }
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.

package dao.impl;


import dao.IUserDAO;
import dao.utils.DaoUtils;
import domain.User;
import java.sql.SQLException;
import java.util.List;
import org.junit.Test;

/**
 * @author Xiao_Lin
 * @date 2021/1/2 20:11
 */
public class UserDAOImplTest {
  IUserDAO userDAO = new UserDAOImpl();
  @Test
  public void insert() {
    userDAO.insert(new User("ghy","123"));

  }

  @Test
  public void delete() {
    userDAO.delete(14);
  }

  @Test
  public void update() {
    userDAO.update(new User(1,"张三","666"));
  }

  @Test
  public void selectAll() {
    List<User> users = userDAO.selectAll();
    users.forEach(System.out::print);
  }

  @Test
  public void selectUserById() {
    User user = userDAO.selectUserById(1);
    System.out.println(user);
  }
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.

//获取连接
Connection conn = JDBCUtils.getConnection();
		
String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);

// 填充占位符
ps.setString(1, "张三");
ps.setString(2, "zs@126.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("zs.png");
ps.setBlob(4, fis);
//执行
ps.execute();
		
fis.close();
JDBCUtils.closeResource(conn, ps);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);

// 填充占位符
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("coffee.png");
ps.setBlob(1, fis);
ps.setInt(2, 25);

ps.execute();

fis.close();
JDBCUtils.closeResource(conn, ps);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 8);
rs = ps.executeQuery();
if(rs.next()){
	Integer id = rs.getInt(1);
    String name = rs.getString(2);
	String email = rs.getString(3);
    Date birth = rs.getDate(4);
	Customer cust = new Customer(id, name, email, birth);
    System.out.println(cust); 
    //读取Blob类型的字段
	Blob photo = rs.getBlob(5);//这里也可以通过列的索引来读取
	InputStream is = photo.getBinaryStream();
	OutputStream os = new FileOutputStream("c.jpg");
	byte [] buffer = new byte[1024];
	int len = 0;
	while((len = is.read(buffer)) != -1){
		os.write(buffer, 0, len);
	}
    JDBCUtils.closeResource(conn, ps, rs);
		
	if(is != null){
		is.close();
	}
		
	if(os !=  null){
		os.close();
	}
    
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.

SELECT * FROM account WHERE name = '张无忌' AND balance >= 1000;
1.

UPDATE account SET balance = balance - 1000 WHERE name = '张无忌';
1.

UPDATE account SET balance = balance + 1000 WHERE name = '赵敏';
1.

import org.junit.Test;

@Test
public void testTx() throws Exception {
// 贾琏欲执事
// 1 查询张无忌的账户余额是否大于等于1000
    Connection conn = JDBCUtil.getConnection();
    String sql = "SELECT * FROM account WHERE balance>=? AND name=?";
    PreparedStatement pst = conn.prepareStatement(sql);
// 给 ? 设置数据
    pst.setBigDecimal(1,new BigDecimal("1000"));
    pst.setString(2,"张无忌");
    ResultSet rs = pst.executeQuery();
    if(!rs.next()){
    System.out.println("余额不足");
    return;
    }
// 2 从张无忌的账户余额中减少1000.
    sql = "UPDATE account SET balance = balance-? WHERE name=?";
    pst = conn.prepareStatement(sql);
//设置? 的数据
    pst.setBigDecimal(1,new BigDecimal("1000"));
    pst.setString(2,"张无忌");
    pst.executeUpdate();
// 模拟出异常
    int a = 10/0;
// 3 在赵敏的账户余额中增加1000.
    sql = "UPDATE account SET balance = balance+? WHERE name=?";
    pst = conn.prepareStatement(sql);
//设置? 的数据
    pst.setBigDecimal(1,new BigDecimal("1000"));
    pst.setString(2,"赵敏");
    pst.executeUpdate();
// 释放资源
    JDBCUtil.close(conn,pst,rs);
    }
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.

SELECT @@tx_isolation;
1.

set transaction isolation level read committed;
1.

set global transaction isolation level read committed;
1.

try{
	//取消事务的自动提交机制,设置为手动提交.
	connection对象.setAutoCommit(false);
	//操作1
	//操作2
	//异常
	//操作3
	//....
	//手动提交事务
	connection对象.commit();
}catch(Exception e){
	//处理异常
    //回滚事务
connection对象.rollback();
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

//使用dbcp数据库连接池的配置文件方式，获取数据库的连接：推荐
// 创建一个DataSource对象
private static DataSource source = null;
static{
	try {
        //创建一个Properties，用于读取配置文件
		Properties pros = new Properties();
        //读取配置文件
		InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("db.properties");
		//加载配置文件
		pros.load(is);
		//根据提供的BasicDataSourceFactory创建对应的DataSource对象
		source = BasicDataSourceFactory.createDataSource(pros);
	} catch (Exception e) {
		e.printStackTrace();
	}
		
}
public static Connection getConnection4() throws Exception {
	Connection conn = source.getConnection();
	return conn;
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useServerPrepStmts=false
username=root
password=123456

initialSize=10
#...
1.
2.
3.
4.
5.
6.
7.

package com.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;

/**
 * @author Xiao_Lin
 * @date 2021/1/3 19:47
 */
public class DruidUtils {
  static DataSource ds = null;
  private DruidUtils(){

  }

  static {
    InputStream stream = Thread.currentThread().getContextClassLoader()
        .getResourceAsStream("db.properties");
    Properties properties = new Properties();
    try {
      properties.load(stream);
      ds = DruidDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public static Connection getConnection(){
    try {
      return ds.getConnection();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.

DriverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql:///db?characterEncoding=utf-8&useSSL=false
username = root
password = 123456
1.
2.
3.
4.