释放双眼,带上耳机,听听看~!
User类,用来封装数据库中每一条user对象
package p01_jdbc;
import java.util.Date;
public class User {
private int id ;
private String username;
private String password;
private String email;
private Date birthday;
public int getId() {
return id;
}
public void setId(int 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;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
JDBC_Utils类,用来提供与数据库连接关闭的方法
package p01_jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBC_Utils {
//获取连接对象的方法
public static Connection getCon(){
Connection con = null ;
try {
//注册对象
Class.forName("com.mysql.jdbc.Driver");
//连接
String jdbc_url = "jdbc:mysql://localhost:3306/jdbc";
String jdbc_user = "root";
String jdbc_password = "root";
con = DriverManager.getConnection(jdbc_url,jdbc_user,jdbc_password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
//关闭连接,释放资源
public static void realse(ResultSet rs,Statement sta,Connection con) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(sta!=null) {
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
sta = null ;
}
if(con!=null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
con = null;
}
}
}
UserDao类,用来提供数据库的操作方法,增删改查等
其中PreparedStatement是预编译对象,使用PreparedStatement可以在sql语句中使用占位符,然后在执行之前为占位符赋值。
package p01_jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* 完成对数据库的增删改查
*
*
* */
public class UserDao {
//提供增加的方法
public Boolean insert (User user) {
Connection con = null ;
Statement sta = null ;
try {
//获取连接
con = JDBC_Utils.getCon();
sta = con.createStatement();
//执行sql
Date birthday = user.getBirthday();
String sqlBirthday = String.format("%tF", birthday);
String sql = "insert into users(id,name,password,email,birthday)values("
+ "'" + user.getId() + "',"
+ "'" + user.getUsername() + "',"
+ "'" + user.getPassword() + "',"
+ "'" + user.getEmail() + "',"
+ "'" + sqlBirthday + "')";
int row = sta.executeUpdate(sql);
if(row > 0 ) {
return true;
}
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
JDBC_Utils.realse(null, sta, con);
}
return false;
}
public List<User> findAllUser(){
Connection con = null ;
Statement sta = null ;
ResultSet rs = null;
try {
//获取连接的对象
con = JDBC_Utils.getCon();
//获取执行sql语句的对象
sta = con.createStatement();
//执行sql语句
String sql = "select * from users";
//遍历结果集
List<User> list = new ArrayList<User>();
rs = sta.executeQuery(sql);
while(rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
java.sql.Date birthday = rs.getDate("birthday");
user.setBirthday(birthday);
list.add(user);
}
return list ;
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
JDBC_Utils.realse(rs, sta, con);
}
}
public User FindId(int id) {
Connection con = null ;
PreparedStatement sta = null ;
ResultSet rs = null;
try {
//获取连接的对象
con = JDBC_Utils.getCon();
//sql语句
String sql = "select * from users where id = ?";
/**
* 获取执行sql语句的预编译对象
* 预编译对象已经在这里传参编译过一次了,所以后面执行的时候不需要在进行传参
* */
sta = con.prepareStatement(sql);
/**
* 设定占位符的值
* 第一个参数是设置第几个占位符
* */
sta.setInt(1, id);
//执行查询
rs = sta.executeQuery();
//遍历结果
if(rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
java.sql.Date birthday = rs.getDate("birthday");
user.setBirthday(birthday);
return user;
}
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
JDBC_Utils.realse(rs, sta, con);
}
return null;
}
}
对于主方法只需要关注传入参数的值即可。
package p01_jdbc;
import java.util.List;
public class FindAllUserTest {
public static void main(String[] args) {
/*
* UserDao dao = new UserDao(); List<User> list = dao.findAllUser();
* System.out.println(list.size());
*/
UserDao dao = new UserDao();
User user = dao.FindId(6);
System.out.println(user.getUsername());
}
}