目录
二、使用ResultSetHandler进行数据库映射和查询
最开始我们要先导DBUtil的包进来
一、最简单直接的数据库查询
1、首先我们要有jdbc.properties文件，在这里我们用的是德鲁伊进行JDBC连接
druid.username=root
druid.password=123456
druid.url=jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
druid.driverClassName=com.mysql.cj.jdbc.Driver
druid.keepAlive=300
druid.maxWait=300
druid.initialSize=10
druid.maxActive=202、然后要有用户类，与数据库中的User表对应
public class User {
private int id;
private String username;
private String password;
public User(int id, String uesrname, String password) {
this.id = id;
this.username = uesrname;
this.password = password;
}
public User() {}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUesrname() {
return username;
}
public void setUesrname(String uesrname) {
this.username = uesrname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", uesrname='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}3、建个工具类，对资源的调用和返回资源
public class PoolUtil {
private static DataSource DATASOURCE = null;
static {
Properties properties = new Properties();
try {
properties.load(PoolTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
} catch (IOException e) {
e.printStackTrace();
}
DATASOURCE = new DruidDataSource();
((DruidDataSource)DATASOURCE).configFromPropety(properties);
}
public static DataSource getDataSource(){
return DATASOURCE;
}
}4、现在是我们的重头戏了，测试类
这个可以对数据库进行查询，我们写的是User类，数据库里对应的一定要是user表，查询的内容会完全展现
public class UtilTest {
@Test
public void testAdd(){
// 最直接的调用
ResultSetHandler<List<User>> h = new BeanListHandler<User>(User.class);
QueryRunner run = new QueryRunner(PoolUtil.getDataSource());
// Execute the query and get the results back from the handler
try {
List<User> result = run.query(
"SELECT * FROM user", h);
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
}
}
}二、使用ResultSetHandler进行数据库映射和查询
1、单条数据的查询
假如我们数据库的表与User类名字对不上，就要用另外一种形式，要先映射再查询，引号里面的名称要与数据库中一样，不然会报错
如此，我们可以查询到id为1的数据库数据，这种写法只能查询单行数据，我试了不写where id = 1，最后出来的是id数最后的那组数据
public class UtilTest {
@Test
public void testAdd(){
ResultSetHandler<User> h = new ResultSetHandler<User>() {
@Override
public User handle(ResultSet resultSet) throws SQLException {
User user = new User();
while (resultSet.next()){
user.setId(resultSet.getInt("id"));
user.setUesrname(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
}
return user;
}
};
QueryRunner run = new QueryRunner(PoolUtil.getDataSource());
// Execute the query and get the results back from the handler
try {
List<User> result = run.query(
"SELECT * FROM user where id = 1", h);
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
}
}
}2、集合数据的查询
这是如何查询数据集合的代码，可以查询user表内所有的数据信息。不过我们还是建议名称一样，这样可以省很多代码，方便快捷。不过这个只能用在简单的查询上面，复杂的查询还是要用ResultSetHandler
public class UtilTest {
@Test
public void testAdd(){
ResultSetHandler<List<User>> h = new ResultSetHandler<List<User>>() {
@Override
public List<User> handle(ResultSet resultSet) throws SQLException {
List<User> users = new ArrayList<>();
while (resultSet.next()){
User user = new User();
user.setId(resultSet.getInt("id"));
user.setUesrname(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
users.add(user);
}
return users;
}
};
QueryRunner run = new QueryRunner(PoolUtil.getDataSource());
try {
List<User> result = run.query(
"SELECT * FROM user", h);
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
}
}
}3、复杂数据库的查询
复杂的查询代码演示，下面的查询代码是将user表和teacher表合并在一起，所以查询出来的结果是两个表的结合。之前的user数据表里面没有和teacher相关的id，所以我们要建一个：
user表，t_id是新建的
teacher表
在数据库的查询里面，我们要查询的语句先写出来，并且对每个数据起个别名
SELECT t.id tid,t.`name` tname,u.id uid,u.username uusername,u.`password` upassword FROM teacher t LEFT JOIN `user` u on t.id = u.t_id
然后我们在Teacher这个类里面加入user表里面的三个数据类型，新加的数据名称可以按别名来，这样好区分，容易记住
public class Teacher {
private int id;
private String name;
private int uId;
private String uUsername;
private String uPassword;
public int getuId() {
return uId;
}
public void setuId(int uId) {
this.uId = uId;
}
public String getuUsername() {
return uUsername;
}
public void setuUsername(String uUsername) {
this.uUsername = uUsername;
}
public String getuPassword() {
return uPassword;
}
public void setuPassword(String uPassword) {
this.uPassword = uPassword;
}
public Teacher(int id, String name) {
this.id = id;
this.name = name;
}
public Teacher(){}
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;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
", uId=" + uId +
", uUsername='" + uUsername + '\'' +
", uPassword='" + uPassword + '\'' +
'}';
}
}
然后在测试类中进行测试，大功告成
public class UtilTest {
@Test
public void testAdd() {
ResultSetHandler<List<Teacher>> h = new ResultSetHandler<List<Teacher>>() {
@Override
public List<Teacher> handle(ResultSet resultSet) throws SQLException {
List<Teacher> teachers = new ArrayList<>();
while (resultSet.next()) {
Teacher teacher = new Teacher();
teacher.setId(resultSet.getInt("tid"));
teacher.setName(resultSet.getString("tname"));
teacher.setuId(resultSet.getInt("uid"));
teacher.setuUsername(resultSet.getString("uusername"));
teacher.setuPassword(resultSet.getString("upassword"));
teachers.add(teacher);
}
return teachers;
}
};
QueryRunner run = new QueryRunner(PoolUtil.getDataSource());
try {
List<Teacher> result = run.query(
"SELECT t.id tid,t.`name` tname,u.id uid,u.username uusername,u.`password` upassword FROM teacher t LEFT JOIN `user` u on t.id = u.t_id", h);
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
}
}
}4、用HashMap进行复杂的数据库查询
如果我们不想污染teacher，可以用HashMap来代替。在操作中老师建议我们使用user类来写，而不是使用map，user看起来会比较舒服，没有那么别扭，但是企业中，map用的非常多
public class UtilTest {
@Test
public void testAdd() {
ResultSetHandler<List<Map<String,Object>>> h = new ResultSetHandler<List<Map<String,Object>>>() {
@Override
public List<Map<String,Object>> handle(ResultSet resultSet) throws SQLException {
List<Map<String,Object>> teachers = new ArrayList<>();
while (resultSet.next()) {
// 8是初始容量,键的内容随意性很大，其实可以随便填
Map<String,Object> teacher = new HashMap<>(8);
teacher.put("tid",resultSet.getInt("tid"));
teacher.put("tname",resultSet.getString("tname"));
teacher.put("uid",resultSet.getInt("uid"));
teacher.put("uusername",resultSet.getString("uusername"));
teacher.put("upassword",resultSet.getString("upassword"));
teachers.add(teacher);
}
return teachers;
}
};
QueryRunner run = new QueryRunner(PoolUtil.getDataSource());
try {
List<Map<String,Object>> result = run.query(
"SELECT t.id tid,t.`name` tname,u.id uid,u.username uusername,u.`password` upassword FROM teacher t LEFT JOIN `user` u on t.id = u.t_id", h);
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
4616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
