JDBC

Tue, May 12, 2020 14-minute read

认识 JDBC

JDBC的演化版本1.0

通过DriveManager得到Connection，得到PreparedStatement, PreparedStatement执行sql返回结果

public class Basic {
    @Test
    public void testJdbc() throws SQLException, ClassNotFoundException {
        // 1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2.建立练连接
        String url = "jdbc:mysql://192.168.56.10:3306/test";
        String user = "root";
        String password = "root";
        Connection conn = DriverManager.getConnection(url, user, password);

        // 3. 创建sql模板
        String sql = "select * from t_user where id = ?";
        PreparedStatement preparedStatement = conn.preparedStatement(sql);

        // 4. 设置模板参考
        preparedStatement.setInt(1, 5);

        // 5. 执行语句
        ResultSet rs = preparedStatement.executeQuery();

        // 6. 处理结果
        while (rs.next()) {
            System.out.println(rs.getObject(1) + "\t" + rs.getObject(2) + "\t" + rs.getObject(3) + "\t" + rs.getObject(4))；
            
        }

        // 7. 释放资源
        rs.close();
        preparedStement.close();
        conn.close();

    }
}

获取Connection的步骤太复杂，需要封装； 资源释放太随意，不够规范，数据库的连接数是有限的，如果不及时释放，会导致其他请求无法访问。应该把释放资源的操作放在finally中，保证资源一定会被关闭。

public class Basic {
    // 抛异常
    @Test
    public void testJdbc() throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            // 1. 获得连接
            conn = JdbcUtils.getConnection();
            String sql = "select * from t_user where id = ?";
            ps = conn.preparedStatement(sql);

            // 4. 设置模板参考
            ps.setInt(1, 5);

            // 5. 执行语句
            rs = ps.executeQuery();

            // 6. 处理结果
            while (rs.next()) {
                System.out.println(rs.getObject(1) + "\t" + rs.getObject(2) + "\t" + rs.getObject(3) + "\t" + rs.getObject(4))；
                
            }
        } finally {
            JdbcUtils.free(rs, ps, conn);
        }
    }
}

JdbcUtils 1.0版本

public class JdbcUtils {

        private static Properties pros = null;

        // 只在JdbcUtils类都加载执行一次
        static {
            // 1. 给pros进行初始化，加载jdbc.properties文件到props对象中
            try {
                InputStream in = JdbcUtils.class.getClassLoader()
                                .getResourceAsStream("jdbc.properties");
                props = new Properties();
                props.load(in);  
            } catch (IOException e) {
                throw new RuntimeException(e);
            }

            //加载驱动类
            try {
                Class.forName(props.getProperty("driver"));
            } catch (ClassNotFoundException e) {
                throw new RuntimeException(e);
            }
        }

        // 获取连接
        public static Connection getConnection() throws SQLException {
            // 得到Connection
            return DriverManager.getConnection(props.getProperty("url"),
                            props.getProperty("username"),
                            props.getProperty("password"));
        }

        // 释放连接
        public static void free (ResultSet rs, Statement st, Connection conn) {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrack();
            } finally {
                try {
                    if (st != null) {
                        st.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrack();
                } finally {
				if (conn != null)
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
            }
        }
    }
}

CRUD

crud

  • dao
    • UserDao(interface)
    • UserDaoJdbcImpl
  • pojo
    • User
  • DAOTest
  • JdbcUtils

UserDao

public interface UserDao {
	int addUser(User user) throws SQLException;
	int update(User user) throws SQLException;
	int delete(User user) throws SQLException;
	User getUser(int Id) throws SQLException;
	User findUser(String name, int age) throws SQLException;
}

UserDaoJdbcImpl

public class UserDaoJdbcImpl implements UserDao {

	public int addUser(User user) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "insert into t_user(name,age, birthday) values (?,?,?) ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getName());
			ps.setInt(2, user.getAge());
			ps.setDate(3, new java.sql.Date(user.getBirthday().getTime()));
			return ps.executeUpdate();
/* 		} catch (SQLExceptoin e) {
            //转为DaoException（运行时异常）抛出，Service层可以不处理                         
			throw new DaoException(e.getMessage(), e); */
        } finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}

	public int delete(User user) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "delete from t_user where id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, user.getId());
			System.out.println(sql);
			return ps.executeUpdate(sql);
		 
        /* 		} catch (SQLExceptoin e) {
            //转为DaoException（运行时异常）抛出，Service层可以不处理                         
			throw new DaoException(e.getMessage(), e); */
        }   finally {
			JdbcUtils.free(rs, ps, conn);
		}

	}

	public int update(User user) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "update t_user set name=?, age=?, birthday=? where id=? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getName());
			ps.setInt(2, user.getAge());
			ps.setDate(3, new java.sql.Date(user.getBirthday().getTime()));
			ps.setInt(4, user.getId());
			return ps.executeUpdate();
            /* 		} catch (SQLExceptoin e) {
            //转为DaoException（运行时异常）抛出，Service层可以不处理                         
			throw new DaoException(e.getMessage(), e); */
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}

	public User findUser(String name, int age) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select id, name, birthday  from t_user where name=? and age=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setInt(2, age);
			rs = ps.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setAge(rs.getInt("age"));
				user.setName(rs.getString("name"));
				user.setBirthday(rs.getDate("birthday"));
			/* 		} catch (SQLExceptoin e) {
            //转为DaoException（运行时异常）抛出，Service层可以不处理                         
			throw new DaoException(e.getMessage(), e); */
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
		return user;
	}

	public User getUser(int userId) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select id, name, age, birthday from t_user where id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setAge(rs.getInt("age"));
				user.setName(rs.getString("name"));
				user.setBirthday(rs.getDate("birthday"));
			/* 		} catch (SQLExceptoin e) {
            //转为DaoException（运行时异常）抛出，Service层可以不处理                         
			throw new DaoException(e.getMessage(), e); */
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
		return user;
	}

DAOTest

public class DAOTest {
	public static void main(String[] args) throws SQLException {
		UserDao userDao = new UserDaoJdbcImpl();
		User user = new User();
		user.setAge(19);
		user.setName("little ming");
		user.setBirthday(new Date());
		userDao.addUser(user);
	}
}

异常处理 上面的CRUD并没有捕获异常，而是直接往外抛。这会带来两个后果：

SQLException是编译时异常，Service在调用DAO时必须处理异常，否则编译不通过。如何处理？要么继续抛，交给Controller处理（意义不大），要么try catch（Service层代码很臃肿，不美观）。 DAO接口有声明异常SQLException，这等于向外界暴露DAO层是JDBC实现。而且针对该接口只能用关系型数据库，耦合度太高了。后期无法切换DAO实现。 比较好的做法是，将SQLException转为运行时异常抛出，Service层可处理也可不处理。

DaoException

public class DaoException extends RuntimeException {
	public DaoException() {
	}

	public DaoException(String message) {
		super(message);
	}

	public DaoException(Throwable cause) {
		super(cause);
	}

	public DaoException(String message, Throwable cause) {
		super(message, cause);
	}
}

模板方法模式重构

繁琐， 如果还有StudentDaoJdbcImpl、TeacherDaoJdbcImpl，那么同样的代码要写好多遍。所以，必须要重构。大体思路是：相同的代码抽取到父类AbstractDao。观察UserDao， 不论是UserDaoLmpl还是StudentDaoJdbcImpl，TeacherDaoJdbcImp， 只有sql模板和设置模板参数的代码不同。可以把sql和参数抽取成父类方法的形参

String sql = “select id, name, age, birthday from t_user where id=?";

ps.setInt(1, userId);

AbstractDao

public abstract class AbstractDao {

	public int addUser(String sql, Object...args)  {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			// String sql = "insert into t_user(name,age, birthday) values (?,?,?) ";

            // sql由调用者传入
			ps = conn.prepareStatement(sql);
			// ps.setString(1, user.getName());
			// ps.setInt(2, user.getAge());
			// ps.setDate(3, new java.sql.Date(user.getBirthday().getTime()));
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+ 1, args[i]);
            }
			return ps.executeUpdate();
		} catch (SQLExceptoin e) {                      
			throw new DaoException(e.getMessage(), e);
        } finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
}

UserDaoImpl

public class UserDaoImpl extends AbstractDao implements UserDao {
    public int addUser(User user) {
        String sql = "insert into t_user(name, age, birthday) values (?,?,?) ";
        Object[] args = new Object[]{user.getName(), user.getAge(), user.getBirthday()};
        
        // 调用父类AbstractDao方法
        return super.update(sql, args);
    }
    public int update(User user) {
		String sql = "update t_user set name=?, age=?, birthday=? where id=? ";
		Object[] args = new Object[]{user.getName(), user.getAge(),
				user.getBirthday(), user.getId()};
		return super.update(sql, args);
	}

	//改
	public int delete(User user) {
		String sql = "delete from t_user where id=?";
		Object[] args = new Object[]{user.getId()};
		return super.update(sql, args);
	}
}

如何抽取查询方法?

user， student都需要用map来装

父类无法制定一个通用代码满足所有子类的结果集映射，因为只有子类自己知道映射规则。所以，我们只能把结果集映射的权利交还给子类去实现。子类如果需要查询，就必须自己实现AbstractDao的rowMapper方法。

AbstractDao

public abstract class AbstractDao {

	public int update(String sql, Object...args)  {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			// String sql = "insert into t_user(name,age, birthday) values (?,?,?) ";

            // sql由调用者传入
			ps = conn.prepareStatement(sql);
			// ps.setString(1, user.getName());
			// ps.setInt(2, user.getAge());
			// ps.setDate(3, new java.sql.Date(user.getBirthday().getTime()));
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+ 1, args[i]);
            }
			return ps.executeUpdate();
		} catch (SQLExceptoin e) {                      
			throw new DaoException(e.getMessage(), e);
        } finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
    //查询
    public List<Object> query(String sql, Object[] args, RowMapper, rowMapper)  {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    List list = new ArrayList<>();
    try {
        conn = JdbcUtils.getConnection();
        // sql由调用者传入
        ps = conn.prepareStatement(sql);
 
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i+ 1, args[i]);
        }
        rs = ps.executeQuery();
        Object obj = null;
        while (rs.next()) {
            Object o = rowMapper.mapRow(rs);
            list.add(o);
        }
        return list;
    } catch (SQLExceptoin e) {                      
        throw new DaoException(e.getMessage(), e);
    } finally {
        JdbcUtils.free(rs, ps, conn);
    }
}

// 定义成抽象方法，让子类去实现
	abstract protected Object rowMapper(ResultSet rs);

UserDaoImpl

public User findUser(String name, int age) {
    String sql =  "select id, name, age, birthday from t_user where id=?";
    Object[] args = new Object[] {name, age};
    Object user = super.query(sql, args);
    return (User) user;
}

public User getUser(int id) {
    String sql =  "select id, name, age, birthday from t_user where name=? and age=?";
    Object[] args = new Object[] {id};
    Object user = super.query(sql, args);
    return (User) user;
}

//UserDaoImpl的结果map
protected Object rowMapper(ResultSet rs) {
     User user = null;
        try {
            user = new User();
           user.setId(rs.getInt("id"));
			user.setAge(rs.getInt("age"));
			user.setName(rs.getString("name"));
			user.setBirthday(rs.getDate("birthday"));
		} catch (SQLException e) {
			throw new DaoException("mapping error");
		}
		return user;
	}
}

假设现在UserDao增加了一个新方法?

public interface UserDao {
	int addUser(User user);
	int update(User user);
	int delete(User user);
	User getUser(int Id);
	User findUser(String name, int age);
	//新增查询方法：根据年龄查询
	List<User> selectUsers(int age);
}

返回值是List，而UserDaoImpl中实现的映射方法rowMapper()只能封装User对象：

直接传方法不行？那我就把这个方法塞进一个对象里，通过对象去调用方法（把需要代理对象执行的代码写在InvocationHandler对象的invoke方法中，再把invocationHandler塞进代理对象供它调用）。

这种模式其实叫策略模式，而且一般是传入接口的实现类。

现在子类已经不需要实现父类的抽象方法了（一个规则无法满足不同返回值映射），改为由子类实现RowMapper接口传入匿名对象的方式，所以AbstractDao中的抽象方法可以删除。也就是说AbstractDao已经没有抽象方法了。于是我把它声明为普通类（可以new），并改名为MyJDBCTemplate。而且，使用MyJDBCTemplate时，我决定不再使用继承，而是选择组合方式（组合比继承灵活）。 RowMapper

public interfae RowMapper {
    // map result 
    Object mapRow(ResultSet rs) throws SQLException;
}

MyJDBCTemplate

public class MyJDBCTmplate {
    //增删改
	public int update(String sql, Object...args)  {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			// String sql = "insert into t_user(name,age, birthday) values (?,?,?) ";

            // sql由调用者传入
			ps = conn.prepareStatement(sql);
			// ps.setString(1, user.getName());
			// ps.setInt(2, user.getAge());
			// ps.setDate(3, new java.sql.Date(user.getBirthday().getTime()));
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+ 1, args[i]);
            }
			return ps.executeUpdate();
		} catch (SQLExceptoin e) {                      
			throw new DaoException(e.getMessage(), e);
        } finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
    //查询
    public Object query(String sql, Object[] args, RowMapper rowMapper)  {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    List list = new ArrayList<>();
    try {
        conn = JdbcUtils.getConnection();
        // sql由调用者传入
        ps = conn.prepareStatement(sql);
 
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i+ 1, args[i]);
        }
        rs = ps.executeQuery();
        Object obj = null;
        while (rs.next()) {
				Object o = rowMapper.mapRow(rs);
				list.add(o);
			}
			return list;
    } catch (SQLExceptoin e) {                      
        throw new DaoException(e.getMessage(), e);
    } finally {
        JdbcUtils.free(rs, ps, conn);
    }
}

UserDaoImpl

public class UserDaoImpl implements UserDao {
    MyDBCTemplate jdbcTemplate = new MyJDBCTemplate();

    public int addUser(User user) {
        String sql = "insert into t_user(name, age, birthday) values (?,?,?) ";
        Object[] args = new Object[]{user.getName(), user.getAge(), user.getBirthday()};
        
        // 调用父类AbstractDao方法
        return jdbcTemplate.update(sql, args);
    }

    
    public int update(User user) {
        String sql = "update t_user set name=?, age=?, birthday=? where id=? ";
        Object[] args = new Object[]{user.getName(), user.getAge(), user.getBirthday(),  user.getId()};
        
        // 调用父类AbstractDao方法
        return jdbcTemplate.update(sql, args);
    }

    public User findUser(String name, int age) {
        String sql =  "select id, name, age, birthday from t_user where id=?";
        Object[] args = new Object[] {name, age};
        Object user = super.query(sql, args);
        return (User) user;
    }

    public int delete(User user) {
    String sql = "delete from t_user where id=?";
    Object[] args = new Object[]{user.getId()};
    //调用jdbcTemplate的update方法
    return jdbcTemplate.update(sql, args);

    public User getUser(int id) {
        String sql =  "select id, name, age, birthday from t_user where id=?";
        Object[] args = new Object[] {id};
        //调用jdbcTemplate的query方法，传入sql，args， RowMapper匿名对象
        List list = jdbcTemplate.query(sql, args, new RowMapper() {
            public Object mapRow(ResultSet rs) throws SQLException {
                User user = new User();
               user.setAge(rs.getInt("age"));
				user.setName(rs.getString("name"));
				user.setBirthday(rs.getDate("birthday"));
				return user;
            }
        });
        
        return (User)list.get(0);
    }

     public User findUser(String name, int age) {
        String sql =  "select id, name, age, birthday from t_user where name=? and age=?";
        Object[] args = new Object[] {name, age};
        //调用jdbcTemplate的query方法，传入sql，args， RowMapper匿名对象
        List list = jdbcTemplate.query(sql, args, new RowMapper() {
            public Object mapRow(ResultSet rs) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
               user.setAge(rs.getInt("age"));
				user.setName(rs.getString("name"));
				user.setBirthday(rs.getDate("birthday"));
				return user;
            }
        });
        
        return (User)list.get(0);
    }

     public List selectUser(int age) {
        String sql =  "select id, name, age, birthday from t_user where age=?";
        Object[] args = new Object[] {age};
        //调用jdbcTemplate的query方法，传入sql，args， RowMapper匿名对象
        List list = jdbcTemplate.query(sql, args, new RowMapper() {
            public Object mapRow(ResultSet rs) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
               user.setAge(rs.getInt("age"));
				user.setName(rs.getString("name"));
				user.setBirthday(rs.getDate("birthday"));
				return user;
            }
        });
        
        return list;
    }

jdbcUtils 2.0

public class JdbcUtils {

        //初始化一个数据库
        private static MyDataSource dataSource = new MyDataSource();

        // 获取连接
        public static Connection getConnection() throws SQLException {
            // 从数据源获取Connection并返回
            return dataSource.getConnection();
        }

          // 获取数据源
        public static MyDataSource getDataSource() {   
            return dataSource;
        }

        // 释放连接
        public static void free (ResultSet rs, Statement st, Connection conn) {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrack();
            } finally {
                try {
                    if (st != null) {
                        st.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrack();
                } finally {
				if (conn != null)
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
            }
        }
    }
}

MyDataSource

public class MyDataSource {

        //数据库信息，用于连接数据库，放进properties里面
       /*  private static String url = "jdbc:mysql://192.168.56.10:3306/test?userSSL-false";
        private static String user = "root";
        private static String password = "root"; */
        private static Properties pros = null;

        // 数据库信息 
        static {
            // 1. 给pros进行初始化，加载jdbc.properties文件到props对象中
            try {
                InputStream in = JdbcUtils.class.getClassLoader()
                                .getResourceAsStream("jdbc.properties");
                props = new Properties();
                props.load(in);  
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
//连接池数据，省略
        private static int initCount = 5;
        private static int currentIdleCount = 0;

//LinkedList充当连接池，removeFirst取出连接，addLast归还连接
        private final static LinkedList<Connection> connectionpool = new LinkedList<>();
    
        public MyDataSource() {
            try {
                for (int i = 0; i < initCount; i++) {
                    // 创建RealConnection
                    Connection realConnection = DriverManager.getConnection(props.getProperty("url"),
                                props.getProperty("username"),
                                props.getProperty("password"));
                                // 将RealConnection传入createProxyConnection(),得到代理连接并加入池中， currentIdleCount++
                    connectionPool.addLast(this.createProxyConnection(realConnection));
                    currentIdleCount++;
                }
                Sout("...连接池初始化结束" + currentIdleCount + "个Connection...")
            } catch
        }

        // 获取连接
        public Connection getConnection() throws SQLException {
            // 同步代码
            synchronized (connectionPool) {

                // 连接池中还有空闲连接，从池中取出，currentIdleCount--

            }
            return connectionPool.removeFirst();
        }


        // 释放连接
        public static void free (ResultSet rs, Statement st, Connection conn) {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrack();
            } finally {
                try {
                    if (st != null) {
                        st.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrack();
                } finally {
				if (conn != null)
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
            }
        }
    }
}

工厂模式重构

User

public class User {
	private Long id;
	private String name;
	private Integer age;
	private Date birthday;

        //省略getter、setter...
}

DaoFactory

public class DaoFactory {
	private static UserDao userDao = null;
	private static DaoFactory daoFactory = new DaoFactory();

	private DaoFactory() {
		try {
			Properties prop = new Properties();
			InputStream inStream = DaoFactory.class.getClassLoader()
					.getResourceAsStream("daoconfig.properties");
			prop.load(inStream);
			//从配置文件中读取UserDao的实现类全类名
			String userDaoClass = prop.getProperty("userDao");
			Class userDaoImplClazz = Class.forName(userDaoClass);
			//反射创建对象
			userDao = (UserDao) userDaoImplClazz.newInstance();
		} catch (Throwable e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	public static DaoFactory getInstance() {
		return daoFactory;
	}

	public UserDao getUserDao() {
		return userDao;
	}
}

daoconfig.properties

userDao = com.test.crudrefactorfinal.dao.UserDaoImpl2

DAOTest

public class DAOTest {
	public static void main(String[] args) {
		//通过工厂得到DAO实现类，如果想换成UserDaoImpl2，修改配置即可
		UserDao userDao = DaoFactory.getInstance().getUserDao();
		List<User> users = userDao.selectUsers(18);
		for (User user : users) {
			System.out.println(user);
		}
	}
}

UserDao

public interface UserDao {
	int addUser(User user);
	int update(User user);
	int delete(User user);
	User getUser(Long Id);
	User findUser(String name, Integer age);
	//新增查询方法：根据年龄查询

    //MyJDBCTemplate中query的返回值设置成List存在局限性。如果用户想映射出Map呢？所以用Object最好
	List<User> selectUsers(Integer age);
}

UserDaoImpl2

public class UserDaoImpl2 implements UserDao {

	MyJDBCTemplate jdbcTemplate = new MyJDBCTemplate();

	//增
	public int addUser(User user) {
		String sql = "insert into t_user(name, age, birthday) values (?,?,?) ";
		Object[] args = new Object[]{user.getName(), user.getAge(),
				user.getBirthday()};
		//调用jdbcTemplate的update方法
		return jdbcTemplate.update(sql, args);
	}

	//删
	public int update(User user) {
		String sql = "update t_user set name=?, age=?, birthday=? where id=? ";
		Object[] args = new Object[]{user.getName(), user.getAge(),
				user.getBirthday(), user.getId()};
		//调用jdbcTemplate的update方法
		return jdbcTemplate.update(sql, args);
	}

	//改
	public int delete(User user) {
		String sql = "delete from t_user where id=?";
		Object[] args = new Object[]{user.getId()};
		//调用jdbcTemplate的update方法
		return jdbcTemplate.update(sql, args);
	}

	public User getUser(Long id) {
		String sql = "select id, name, age, birthday from t_user where id=?";
		Object[] args = new Object[]{id};
		//调用jdbcTemplate的query方法
	// 	List list = jdbcTemplate.query(sql, args, new RowMapper() {
	// 		public Object mapRow(ResultSet rs) throws SQLException {
	// 			User user = new User();
	// 			user.setId(rs.getInt("id"));
	// 			user.setAge(rs.getInt("age"));
	// 			user.setName(rs.getString("name"));
	// 			user.setBirthday(rs.getDate("birthday"));
	// 			return user;
	// 		}
	// 	});
	// 	return (User)list.get(0);
	// }
    //调用jdbcTemplate的query方法
        Object query = jdbcTemplate.query(sql, args, new BeanHandler(user.class));
        return (User)query;

	public User findUser(String name, Integer age) {
		String sql = "select id, name, age, birthday from t_user where name=? and age=?";
		Object[] args = new Object[]{name, age};
		//调用jdbcTemplate的query方法
	// 	List list = jdbcTemplate.query(sql, args, new RowMapper() {
	// 		public Object mapRow(ResultSet rs) throws SQLException {
	// 			User user = new User();
	// 			user.setId(rs.getInt("id"));
	// 			user.setAge(rs.getInt("age"));
	// 			user.setName(rs.getString("name"));
	// 			user.setBirthday(rs.getDate("birthday"));
	// 			return user;
	// 		}
	// 	});
	// 	return (User)list.get(0);
	// }
       Object query = jdbcTemplate.query(sql, args, new BeanHandler(user.class));
        return (User)query;


	public List selectUsers(Integer age) {
		String sql = "select id, name, age, birthday from t_user where age=?";
		Object[] args = new Object[]{age};
        // new BeanHandler,每次都让用户自己写一个匿名内部类实在太烦了，而且findUser和getUser方法返回值都是User，会重复。返回值类型其实是可以穷举的，比如单个Bean，List<Bean>、Map、List<Map>等。我们预先定义几个映射器供用户使用.
        Object query = jdbcTemplate.query(sql, args, new BeanHandler(user.class));
        return (List)query;
	}
}

RowMapper

public interface ResultSetHandler {
	//映射结果集
	Object handler(ResultSet rs) ;
}

public class MyJDBCTemplate {
	// 增删改
	public int update(String sql, Object...args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			// sql由调用者传入
			ps = conn.prepareStatement(sql);
			// 遍历设置模板参数
			for (int i = 0; i < args.length; i++){
				ps.setObject(i + 1, args[i]);
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw new DaoException(e.getMessage(), e);
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}

	//查询
	public Object query(String sql, Object[] args, ResultSetHandler resultSetHandler) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			// sql由调用者传入
			ps = conn.prepareStatement(sql);
			// 遍历设置模板参数
			for (int i = 0; i < args.length; i++)
				ps.setObject(i + 1, args[i]);
			rs = ps.executeQuery();
			// 映射规则由子类传入

            //MyJDBCTemplate中query的返回值设置成List存在局限性。如果用户想映射出Map呢？所以用Object最好
			return resultSetHandler.handler(rs);
		} catch (SQLException e) {
			throw new DaoException(e.getMessage(), e);
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
}

Listener

注解

public @interface 注解名称{
    属性列表;
}

public interface 注解名称 extends Annotation{
    属性列表;
}

ThreadLocal

ThreadLocal其实不存东西，ThreadLocalMap的key也不是Thread，是ThreadLocal。

title: “Spring note”

date: 2021-06-12T15:56:39+08:00 Description: “learning note of Spring 5” Tags: [ “IOC”,“AoP”,“JdbcTemplate”,“事务管理”, “Spring5 framework” ] Categories: [] DisableComments: false

Spring JDBC ORM

模板方法，建造者模式

st=>start: Start:>http://www.google.com[blank]
e=>end:>http://www.google.com
op1=>operation: My Operation
sub1=>subroutine: My Subroutine
cond=>condition: Yes
or No?:>http://www.google.com
io=>inputoutput: catch something...
para=>parallel: parallel tasks

st->op1->cond
cond(yes)->io->e
cond(no)->para
para(path1, bottom)->sub1(right)->op1
para(path2, top)->op1

本地 branch 和 远程 branch 挂钩错误

PS F:\笔记\Spring\minSpring> git status On branch InstanStrtg Your branch is up to date with ‘origin/BeanDefinition-BeanDefinitionRegistry’