JDBC--面向对象实现

86 阅读 0 评论 57 点赞

我是靠谱客的博主故意指甲油，最近开发中收集的这篇文章主要介绍JDBC--面向对象实现，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

JDBC API工作的四个环节：
1.DriverManager类：依据数据库的不同，管理JDBC驱动。
2.Connection接口：负责连接数据库并担任传送数据的任务。
3.Statement接口：由Connection产生，负责执行SQL语句。
4.ResultSet接口：负责保存Statement执行后所产生的查询结果。

一、bin包

package bean;
/*
1.对应数据库中的表UserInfo
2.bean包中的类一般都以Info结尾，以表名开头，首字母大写
*/
public class UserInfo {
/*
1.属性和表中的列一一对应
2.属性名首字母小写，驼峰
3.属性和列类型对应
4.属性名尽量和列名一致 user_id--->userId
*/
private Integer userId;
private String userName;
private String userSex;
private Integer userAge;
private String userAddress;
private String userPass;
private Integer userState;
/*封装类
必写！ alt+insert
1.get set方法
2.构造器
3.toString（）方法
*/
public UserInfo() {
}
public Integer getUserId() {
return userId;
}
public String getUserName() {
return userName;
}
public String getUserSex() {
return userSex;
}
public Integer getUserAge() {
return userAge;
}
public String getUserAddress() {
return userAddress;
}
public String getUserPass() {
return userPass;
}
public Integer getUserState() {
return userState;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public void setUserName(String userName) {
this.userName = userName;
}
public void setUserSex(String userSex) {
this.userSex = userSex;
}
public void setUserAge(Integer userAge) {
this.userAge = userAge;
}
public void setUserAddress(String userAddress) {
this.userAddress = userAddress;
}
public void setUserPass(String userPass) {
this.userPass = userPass;
}
public void setUserState(Integer userState) {
this.userState = userState;
}
@Override
public String toString() {
return "UserInfo{" +
"userId=" + userId +
", userName='" + userName + ''' +
", userSex='" + userSex + ''' +
", userAge=" + userAge +
", userAddress='" + userAddress + ''' +
", userPass='" + userPass + ''' +
", userState=" + userState +
'}'+"n";
}
}

二、db包

package db;
import java.sql.*;
//负责连接和关闭数据库
public class DBManager {
private DBManager(){
}
/**
* 这个能够获取到数据库的连接
* user:root
* passwd:root
* 数据库：db1
* @return
*/
public static Connection getConnection(){
Connection conn = null;
try{
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url,user,password);
}catch(ClassNotFoundException e){
System.out.println("未找到类！");
}catch(SQLException e){
System.out.println(e.getMessage());
}
return conn;
}
public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
try{
if (rs!=null){
rs.close();
}
ps.close();
conn.close();
}catch(SQLException e){
System.out.println(e.getMessage());
}
}
}

三、dao包

package dao;
import bean.UserInfo;
import db.DBManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/*
1.DAO:Data Access Object
2.一般以bean包的类名为前缀
3.dao包中类负责执行CRUD操作
4.一个dao类负责一个表的CRUD，也可以说成是对一个bean类的CRUD（增删改查）
*/
public class UserInfoDAO {
private Connection conn;
private PreparedStatement ps;
private ResultSet rs;
//根据主键获取对应行
public UserInfo findUserById(int userId){
UserInfo ui = new UserInfo();
String sql = "select * from userinfo where userid = ?";
conn = DBManager.getConnection();
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, userId);
rs = ps.executeQuery();
if (rs.next()) {
ui.setUserId(rs.getInt(1));
ui.setUserName(rs.getString(2));
ui.setUserSex(rs.getString(3));
ui.setUserAge(rs.getInt(4));
ui.setUserAddress(rs.getString(5));
ui.setUserPass(rs.getString(6));
ui.setUserState(rs.getInt(7));
}
}catch(SQLException e){
System.out.println(e.getMessage());
}finally {
DBManager.close(conn,ps,rs);
}
return ui;
}
//获取所有行数据
public List<UserInfo> findAllUser(){
List<UserInfo> list = new ArrayList<>();
String sql = "select * from userinfo where userstate = 1";
conn = DBManager.getConnection();
try{
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
UserInfo ui = new UserInfo();//放到外面则只有一条重复的数据，因为只有一个对象
ui.setUserId(rs.getInt(1));
ui.setUserName(rs.getString(2));
ui.setUserSex(rs.getString(3));
ui.setUserAge(rs.getInt(4));
ui.setUserAddress(rs.getString(5));
ui.setUserPass(rs.getString(6));
ui.setUserState(rs.getInt(7));
list.add(ui);
}
}catch(SQLException e){
System.out.println(e.getMessage());
}finally {
DBManager.close(conn,ps,rs);
}
return list;
}
//添加用户信息到数据库
public boolean saveUser(UserInfo ui){
boolean b = false;
String sql = "insert into userinfo values(?,?,?,?,?,md5(?),1)";
conn = DBManager.getConnection();
try{
ps = conn.prepareStatement(sql);
ps.setInt(1,ui.getUserId());
ps.setString(2,ui.getUserName());
ps.setString(3,ui.getUserSex());
ps.setInt(4,ui.getUserAge());
ps.setString(5,ui.getUserAddress());
ps.setString(6,ui.getUserPass());
int result = ps.executeUpdate();
System.out.println("共"+result+"行受到影响");
if (result>0){
b = true;
}
}catch(SQLException e){
System.out.println(e.getMessage());
}finally {
DBManager.close(conn,ps,null);
}
return b;
}
//删除指定用户 非推荐写法
public boolean removeUser(int userId){
boolean b = false;
String sql = "delete from userinfo where userid = ?";
conn = DBManager.getConnection();
try{
ps = conn.prepareStatement(sql);
ps.setInt(1,userId);
int result = ps.executeUpdate();
if (result>0){
b = true;
}
}catch(SQLException e){
System.out.println(e.getMessage());
}finally {
DBManager.close(conn,ps,null);
}
return b;
}
//删除指定用户信息，其实是修改了状态
推荐写法
public boolean deleteUser(int userId){
boolean b = false;
String sql = "update userinfo set userstate = 0 where userid = ?";
conn = DBManager.getConnection();
try{
ps = conn.prepareStatement(sql);
ps.setInt(1,userId);
int n = ps.executeUpdate();
if (n>0){
b = true;
}
}catch(SQLException e){
System.out.println(e.getMessage());
}finally {
DBManager.close(conn,ps,null);
}
return b;
}
//修改指定用户信息，不带修改密码
public boolean updateUser(UserInfo ui){
boolean b = false;
String sql = "update userinfo set username = ?,usersex = ?,userage = ?,userAddress = ? where userId = ?";
conn = DBManager.getConnection();
try{
ps = conn.prepareStatement(sql);
ps.setInt(5,ui.getUserId());
ps.setString(1,ui.getUserName());
ps.setString(2,ui.getUserSex());
ps.setInt(3,ui.getUserAge());
ps.setString(4,ui.getUserAddress());
int result = ps.executeUpdate();
if (result>0){
b = true;
}
}catch(SQLException e){
System.out.println(e.getMessage());
}finally {
DBManager.close(conn,ps,null);
}
return b;
}
//分页
public List<UserInfo> findUserByPage(int page,int size){
List<UserInfo> list = new ArrayList<>();
String sql = "select * from userinfo where userstate = 1 limit ?,?";
conn = DBManager.getConnection();
try{
ps = conn.prepareStatement(sql);
ps.setInt(1,(page-1)*size);
ps.setInt(2,size);
rs = ps.executeQuery();
while(rs.next()){
UserInfo ui = new UserInfo();
ui.setUserId(rs.getInt(1));
ui.setUserName(rs.getString(2));
ui.setUserSex(rs.getString(3));
ui.setUserAge(rs.getInt(4));
ui.setUserAddress(rs.getString(5));
ui.setUserPass(rs.getString(6));
ui.setUserState(rs.getInt(7));
list.add(ui);
}
}catch(SQLException e){
System.out.println(e.getMessage());
}finally {
DBManager.close(conn,ps,rs);
}
return list;
}
}

四、test

package test;
import bean.UserInfo;
import dao.UserInfoDAO;
import javax.jws.soap.SOAPBinding;
import java.util.List;
public class Test {
private static UserInfoDAO uidao = new UserInfoDAO();
public static void a(){
UserInfo ui = uidao.findUserById(1);
System.out.println(ui);
}
public static void b(){
List<UserInfo> list = uidao.findAllUser();
System.out.println(list);
}
public static void c(){
UserInfo us = new UserInfo();
us.setUserId(88);
us.setUserName("bbb");
us.setUserSex("男");
us.setUserAge(18);
us.setUserAddress("北京");
us.setUserPass("123");
us.setUserState(1);
boolean judge = uidao.saveUser(us);
System.out.println(judge?"插入成功！":"插入失败！");
}
public static void d(){
boolean judge = uidao.removeUser(88);
System.out.println(judge?"删除成功！":"删除失败！");
}
public static void e(){
boolean judge = uidao.deleteUser(87);
System.out.println(judge?"删除成功！":"删除失败！");
}
public static void f(){
UserInfo us = new UserInfo();
us.setUserId(86);
us.setUserName("ddd");
us.setUserSex("男");
us.setUserAge(18);
us.setUserAddress("北京");
us.setUserPass("123");
us.setUserState(1);
boolean judge = uidao.updateUser(us);
System.out.println(judge?"更改成功！":"更改失败！");
}
public static void g(int page,int size){
List<UserInfo> list = uidao.findUserByPage(page,size);
System.out.println(list);
}
public static void main(String[] args) {
g(1,10);
}
}