手写JDBC连接,并转换实体
说明:通过jdbc查询数据库,并且将数据装换成实体对象。
本猿数据库是5.7,故引入了mysql-connector-java-5.1.38.jar
注:后续会加上自定义注解,模仿MyBatis、Hibernate等持久层框架解析实体。
总体步骤:
- 加载驱动类
- 获取connection
- 编写sql语句
- 预处理SQL语句
- 获取结果集
- 解析结果集,并转换成实体
- 释放资源
直接上代码
User.java
package com.wdt.tools.database.entity;
public class User {
private Long id;
private String userName;
private String password;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
JdbcTest.java
package com.wdt.tools.database;
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 com.wdt.tools.database.entity.User;
public class JdbcTest {
public Connection getConnection() {
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?characterEncoding=UTF-8", "root", "123456");
return connection;
} catch (Exception e) {
// TODO: handle exception
throw new RuntimeException(e.getMessage());
}
}
public List<User> getUsers() {
List<User> users = new ArrayList<User>();
Connection connection = getConnection();
try {
String sql = "select * from tb_user";
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
//下标从1开始
while (rs.next()) {
User user = new User();
user.setId(rs.getLong(1));
user.setUserName(rs.getString(2));
user.setPassword(rs.getString(3));
users.add(user);
}
return users;
} catch (Exception e) {
// TODO: handle exception
throw new RuntimeException(e.getMessage());
}
}
public void connection() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//通过驱动类获取数据库连接connection
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?characterEncoding=UTF-8", "root", "123456");
String sql = "select * from tb_user u where u.user_name = ?";
preparedStatement = connection.prepareStatement(sql);
//预处理,处理参数序数从1开始
preparedStatement.setString(1, "本猿");
//向数据库发起sql查询
resultSet = preparedStatement.executeQuery();
System.out.println("访问数据库成功!!!");
int count = 0;
//遍历结果集
while (resultSet.next()) {
System.out.println(resultSet.getString("user_name") + "-" + resultSet.getString("password"));
count++;
}
System.out.println(String.format("获得结果集%d条", count));
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public static void main(String[] args) {
JdbcTest jdbc = new JdbcTest();
List<User> users = jdbc.getUsers();
for (User user : users) {
System.out.println(user.getUserName());
}
}
}