手写JDBC连接，并转换实体

手写JDBC连接，并转换实体

说明：通过jdbc查询数据库，并且将数据装换成实体对象。

本猿数据库是5.7，故引入了mysql-connector-java-5.1.38.jar

注：后续会加上自定义注解，模仿MyBatis、Hibernate等持久层框架解析实体。

总体步骤：

1. 加载驱动类
2. 获取connection
3. 编写sql语句
4. 预处理SQL语句
5. 获取结果集
6. 解析结果集，并转换成实体
7. 释放资源

直接上代码

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());
		}
		
		
	}
}