jdbc基本操作:
(1)加载并注册数据库驱动。
(2)通过DriverManager获取数据库连接。
(3)通过Connection对象获取Statement对象。
(4)使用Statement执行SQL语句。
(5)操作ResultSet结果集。
(6)关闭连接,释放资源。
数据库的操作其实都差不多,我们可以把相同的内容写成方法、工具类,这样可以极大地减小耦合度,也方便我们以后的套用,可以无限套娃。
这里用的是mysql数据库
下面看看具体操作
第一步先建数据表吧
good商品表
CREATE TABLE `good` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '商品id',
`name` varchar(20) NOT NULL COMMENT '商品名称',
`price` float NOT NULL COMMENT '商品价格',
`stock` int(10) DEFAULT NULL COMMENT '商品库存',
`count` int(10) unsigned zerofill DEFAULT '0000000000' COMMENT '订单量',
`imgUrl` longtext CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '商品图片',
`type` varchar(20) DEFAULT NULL COMMENT '商品类型',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=111121 DEFAULT CHARSET=utf8
user用户表
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`name` varchar(20) NOT NULL COMMENT '用户名',
`password` varchar(16) NOT NULL COMMENT '密码',
`mobile` int(13) DEFAULT NULL COMMENT '电话号码',
`qq` int(12) DEFAULT NULL COMMENT 'QQ号码',
`signinTime` datetime DEFAULT NULL COMMENT '注册时间',
`count` int(10) DEFAULT NULL COMMENT '购买次数',
`address` varchar(255) DEFAULT NULL COMMENT '用户地址',
`role` varchar(10) DEFAULT NULL COMMENT '用户角色',
`email` varchar(255) DEFAULT NULL COMMENT '用户邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11113 DEFAULT CHARSET=utf8
底层都是一样的,可以写个工具类
.
DBUtil.java工具类
package com.xmj.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
private final static String DRIVER = "com.mysql.cj.jdbc.Driver";
private final static String URL ="jdbc:mysql://127.0.0.1:3306/你的数据库名?useSSL=false&serverTimezone=UTC";
private final static String USERNAME = "用户名";
private final static String PASSWORD = "密码";
public static Connection connection = null;
public static PreparedStatement pstmt = null;
public static ResultSet rs = null;
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName(DRIVER);
return DriverManager.getConnection(URL,USERNAME,PASSWORD);
}
public static PreparedStatement createPreparedStatement(String sql,Object[] params) throws SQLException, ClassNotFoundException {
pstmt = getConnection().prepareStatement(sql);
if(params!=null)
for(int i=0;i<params.length;i++) {
pstmt.setObject(i+1, params[i]);
}
return pstmt;
}
//查询总数
public static int getTotalCount(String sql) {
int count = -1;
try {
pstmt = createPreparedStatement(sql, null);
ResultSet rs = pstmt.executeQuery();
if(rs.next()) {
count = rs.getInt(1);
}
}catch (ClassCastException e) {
// TODO: handle exception
e.printStackTrace();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
closeAll(rs, pstmt, connection);
}
return count;
}
//增删改
public static boolean excuteUpdate(String sql,Object[] params) throws ClassNotFoundException {
try {
pstmt = createPreparedStatement(sql,params);
int count = pstmt.executeUpdate();
if(count>0) return true;
else return false;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return false;
}finally{
closeAll(null,pstmt,connection);
}
}
//查
public static ResultSet executeQuery(String sql,Object[] params) {
try {
pstmt = createPreparedStatement(sql,params);
rs = pstmt.executeQuery();
return rs;
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
}
finally{
closeAll(null,null,connection);
}
}
public static void closeAll(ResultSet rs ,Statement stmt,Connection connection) {
try {
if(rs!=null)rs.close();
// if(pstmt!=null)pstmt.close();
if(connection!=null)connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Good实体类
package com.xmj.entity;
public class Good {
private Integer id;
private String name;
private Float price;
private Integer stock;
private Integer count;
private String imgUrl;
private String type;
/**
* @return the id
*/
public Integer getId() {
return id;
}
public Good(String name, Float price, Integer stock, Integer count, String imgUrl, String type) {
super();
this.name = name;
this.price = price;
this.stock = stock;
this.count = count;
this.imgUrl = imgUrl;
this.type = type;
}
/**
* @param id the id to set
*/
public void setId(Integer id) {
this.id = id;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the price
*/
public Float getPrice() {
return price;
}
/**
* @param price the price to set
*/
public void setPrice(Float price) {
this.price = price;
}
/**
* @return the stock
*/
public Integer getStock() {
return stock;
}
/**
* @param stock the stock to set
*/
public void setStock(Integer stock) {
this.stock = stock;
}
/**
* @return the count
*/
public Integer getCount() {
return count;
}
/**
* @param count the count to set
*/
public void setCount(Integer count) {
this.count = count;
}
/**
* @return the imgUrl
*/
public String getImgUrl() {
return imgUrl;
}
/**
* @param imgUrl the imgUrl to set
*/
public void setImgUrl(String imgUrl) {
this.imgUrl = imgUrl;
}
/**
* @return the type
*/
public String getType() {
return type;
}
/**
* @param type the type to set
*/
public void setType(String type) {
this.type = type;
}
public Good(Integer id, String name, Float price, Integer stock, Integer count, String imgUrl, String type) {
super();
this.id = id;
this.name = name;
this.price = price;
this.stock = stock;
this.count = count;
this.imgUrl = imgUrl;
this.type = type;
}
public Good() {
super();
}
}
user实体类
package com.xmj.entity;
import java.util.Date;
public class User {
private Integer id;
private String name;
private String password;
private Integer mobile;
private String role;
private Integer QQ;
private String email;
private Date signinTime;
private Integer count;
private String address;
/**
* @return the id
*/
public Integer getId() {
return id;
}
/**
* @param id the id to set
*/
public void setId(Integer id) {
this.id = id;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the password
*/
public String getPassword() {
return password;
}
/**
* @param password the password to set
*/
public void setPassword(String password) {
this.password = password;
}
/**
* @return the mobile
*/
public Integer getMobile() {
return mobile;
}
/**
* @param mobile the mobile to set
*/
public void setMobile(Integer mobile) {
this.mobile = mobile;
}
/**
* @return the role
*/
public String getRole() {
return role;
}
/**
* @param role the role to set
*/
public void setRole(String role) {
this.role = role;
}
/**
* @return the qQ
*/
public Integer getQQ() {
return QQ;
}
/**
* @param qQ the qQ to set
*/
public void setQQ(Integer qQ) {
QQ = qQ;
}
/**
* @return the email
*/
public String getEmail() {
return email;
}
/**
* @param email the email to set
*/
public void setEmail(String email) {
this.email = email;
}
/**
* @return the signinTime
*/
public Date getSigninTime() {
return signinTime;
}
/**
* @param signinTime the signinTime to set
*/
public void setSigninTime(Date signinTime) {
this.signinTime = signinTime;
}
/**
* @return the count
*/
public Integer getCount() {
return count;
}
/**
* @param count the count to set
*/
public void setCount(Integer count) {
this.count = count;
}
/**
* @return the address
*/
public String getAddress() {
return address;
}
/**
* @param address the address to set
*/
public void setAddress(String address) {
this.address = address;
}
public User(Integer id, String name, String password, Integer mobile, String role, Integer qQ, String email,
Date signinTime, Integer count, String address) {
super();
this.id = id;
this.name = name;
this.password = password;
this.mobile = mobile;
this.role = role;
QQ = qQ;
this.email = email;
this.signinTime = signinTime;
this.count = count;
this.address = address;
}
public User(String name, String password, Integer mobile, String role, Integer qQ, String email,
Date signinTime, Integer count, String address) {
super();
this.name = name;
this.password = password;
this.mobile = mobile;
this.role = role;
QQ = qQ;
this.email = email;
this.signinTime = signinTime;
this.count = count;
this.address = address;
}
}
泛型的好处就是,我们不用定义类型,让你的代码更通用。比如我们需要返回类型是一个User类和Good类,不使用泛型就需要写两个方法,如果使用泛型,就只需要用T来泛指未知类,在我们调用的时候,直接用我们需要的类就可以了。
IMapper.java泛型接口
package com.xmj.mapper;
import java.sql.ResultSet;
import java.util.List;
import com.xmj.entity.DataVO;
import com.xmj.entity.Good;
import com.xmj.entity.Order;
import com.xmj.entity.User;
public interface IMapper<T> {
//查总数
public int getTotalCount();
//判断是否存在
public boolean isExist(int id) ;
//增
public boolean add(T t);
//改
public boolean updateById(int id, T good) throws ClassNotFoundException;
//删
public boolean deleteById(int sno) ;
//查全部
public List<T> queryAll();
//查,限制页数
public List<T> queryByPage(int currentPage, int pageSize);
//根据id查
public T queryById(int id);
}
GoodMapper进行持久化操作,实现IMapper接口,然后实现接口的方法
GoodMapper持久化层
package com.xmj.mapper;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.xmj.entity.DataVO;
import com.xmj.entity.Good;
import com.xmj.entity.Order;
import com.xmj.util.DBUtil;
public class GoodMapper implements IMapper<Good>{
@Override
public int getTotalCount() {
String sql = "select count(*) from good;";
return DBUtil.getTotalCount(sql);
}
@Override
public boolean isExist(int id) {
return queryById(id)!=null?true:false;
}
@Override
public boolean add(Good good) {
String sql = "insert into good(name,price,stock,count,imgUrl,type)values(?,?,?,?,?,?)";
Object[] params = {good.getName(),good.getPrice(),good.getStock(),good.getCount(),good.getImgUrl(),good.getType()};
try {
return DBUtil.excuteUpdate(sql, params);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
@Override
public boolean updateById(int id, Good good) throws ClassNotFoundException {
String sql = "update good set name=?,price=?,stock=?,count=?,imgUrl=?,type=? where id=?";
Object[] params = {good.getName(),good.getPrice(),good.getStock(),good.getCount(),good.getImgUrl(),good.getType(),id};
return DBUtil.excuteUpdate(sql, params);
}
@Override
public boolean deleteById(int id) {
String sql = "delete from good where id=?";
Object[] params = {id};
try {
return DBUtil.excuteUpdate(sql, params);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
@Override
public Good queryById(int id) {
PreparedStatement pstmt = null;
Good good = null;
// List<Good> goods = new ArrayList<>();
ResultSet rs = null;
try {
String sql = "select * from good where id="+id;
rs = DBUtil.executeQuery(sql, null);
if(rs.next()) {
int theid = rs.getInt("id");
String name = rs.getString("name");
Float price = rs.getFloat("price");
int count = rs.getInt("count");
int stock = rs.getInt("stock");
String imgUrl = rs.getString("imgUrl");
String type = rs.getString("type");
good = new Good(theid,name,price,stock,count,imgUrl,type);
// goods.add(good);
}
return good;
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
}finally{
DBUtil.closeAll(rs, pstmt,DBUtil.connection);
}
}
@Override
public List<Good> queryAll() {
PreparedStatement pstmt = null;
Good good = null;
List<Good> goods = new ArrayList<>();
ResultSet rs = null;
try {
String sql = "select * from good";
rs = DBUtil.executeQuery(sql, null);
while(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
Float price = rs.getFloat("price");
int count = rs.getInt("count");
int stock = rs.getInt("stock");
String imgUrl = rs.getString("imgUrl");
String type = rs.getString("type");
good = new Good(id,name,price,stock,count,imgUrl,type);
goods.add(good);
}
return goods;
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
}finally{
DBUtil.closeAll(null, DBUtil.pstmt,DBUtil.connection);
}
}
@Override
public List<Good> queryByPage(int currentPage, int pageSize) {
String sql = "select * from good limit "+currentPage*pageSize+","+pageSize+";";
Object[] params = {currentPage*pageSize,(currentPage-1)*pageSize+1};
ResultSet rs = DBUtil.executeQuery(sql, params);
List<Good> goods = new ArrayList<>();
try {
while(rs.next()) {
Good good = new Good(rs.getInt("id"),rs.getString("name"),rs.getFloat("price"),rs.getInt("stock"),rs.getInt("count"),rs.getString("imgUrl"),rs.getString("type"));
goods.add(good);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return goods;
}
}
GoodMapper进行持久化操作,同样实现IMapper接口的方法,这时候你会发现,可以直接把上面的GoodMappr实现的方法复制粘贴过来,稍微修改一下就可以了。这样是不是提高了效率呢
UserMapper持久化层
package com.xmj.mapper;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.xmj.entity.DataVO;
import com.xmj.entity.User;
import com.xmj.util.DBUtil;
public class UserMapper implements IMapper<User>{
@Override
public int getTotalCount() {
String sql = "select count(*) from user;";
return DBUtil.getTotalCount(sql);
}
@Override
public boolean isExist(int id) {
return queryById(id)!=null?true:false;
}
@Override
public boolean add(User user) {
String sql = "insert into user(name,password,mobile,qq,signinTime,count,address,role,email)values(?,?,?,?,?,?,?,?,?)";
Object[] params = {user.getName(),user.getPassword(),user.getMobile(),user.getQQ(),user.getSigninTime(),user.getCount(),user.getAddress(),user.getRole(),user.getEmail()};
try {
return DBUtil.excuteUpdate(sql, params);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
@Override
public boolean updateById(int id, User user) throws ClassNotFoundException {
String sql = "update user set name=?,password=?,mobile=?,qq=?,signinTime=?,count=?,address=?,role=?,email=? where id=?";
Object[] params = {user.getName(),user.getPassword(),user.getMobile(),user.getQQ(),user.getSigninTime(),user.getCount(),user.getAddress(),user.getRole(),user.getEmail(),id};
return DBUtil.excuteUpdate(sql, params);
}
@Override
public boolean deleteById(int id) {
String sql = "delete from user where id=?";
Object[] params = {id};
try {
return DBUtil.excuteUpdate(sql, params);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
@Override
public User queryById(int id) {
PreparedStatement pstmt = null;
User user = null;
// List<Good> goods = new ArrayList<>();
ResultSet rs = null;
try {
String sql = "select * from user where id="+id;
rs = DBUtil.executeQuery(sql, null);
if(rs.next()) {
String name = rs.getString("name");
String password = rs.getString("password");
int mobile = rs.getInt("mobile");
int qq = rs.getInt("qq");
Date signinTime = rs.getDate("signinTime");
Integer count = rs.getInt("count");
String address = rs.getString("address");
String role = rs.getString("role");
String email = rs.getString("email");
user = new User(id,name,password,mobile,role,qq,email,signinTime,count,address);
// goods.add(good);
}
return user;
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
}finally{
DBUtil.closeAll(rs, pstmt,DBUtil.connection);
}
}
@Override
public List<User> queryAll() {
PreparedStatement pstmt = null;
User user = null;
List<User> users = new ArrayList<>();
ResultSet rs = null;
try {
String sql = "select * from user";
rs = DBUtil.executeQuery(sql, null);
while(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String password = rs.getString("password");
int mobile = rs.getInt("mobile");
int qq = rs.getInt("qq");
Date signinTime = rs.getDate("signinTime");
Integer count = rs.getInt("count");
String address = rs.getString("address");
String role = rs.getString("role");
String email = rs.getString("email");
user = new User(id,name,password,mobile,role,qq,email,signinTime,count,address);
users.add(user);
}
return users;
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
}finally{
DBUtil.closeAll(null, DBUtil.pstmt,DBUtil.connection);
}
}
@Override
public List<User> queryByPage(int currentPage, int pageSize) {
String sql = "select * from user limit "+currentPage*pageSize+","+pageSize+";";
Object[] params = {currentPage*pageSize,(currentPage-1)*pageSize+1};
ResultSet rs = DBUtil.executeQuery(sql, params);
List<User> users = new ArrayList<>();
try {
while(rs.next()) {
User user = new User(rs.getInt("id"),rs.getString("name"),rs.getString("password"),rs.getInt("mobile"),rs.getString("role"),rs.getInt("qq"),rs.getString("email"),rs.getDate("signinTime"),rs.getInt("count"),rs.getString("address"));
users.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return users;
}
}
接下来就是调用了,先写个业务层吧,如果要用到Layui,就需要按指定格式返回数据,所以在分页查询封装了一下数据,这个就按你自己不同的需求来实现吧。
GoodService业务层
package com.xmj.service;
import java.util.List;
import com.xmj.entity.DataVO;
import com.xmj.entity.Good;
import com.xmj.mapper.GoodMapper;
public class GoodService {
private GoodMapper goodMapper = new GoodMapper();
//分页查询
public DataVO<Good> queryByPage(int currentPage,int pageSize){
DataVO<Good> dataVO = new DataVO<Good>();
List<Good> goods = goodMapper.queryByPage(currentPage,pageSize);
dataVO.setData(goods);
dataVO.setCode(0);
dataVO.setMsg("");
dataVO.setCount(goods.size());
return dataVO;
}
//查询当前页的数据集合
public int getTotalCount() {
return goodMapper.getTotalCount();
}
//删
public int deleteGoodById(int id) {
if(goodMapper.isExist(id)) {
goodMapper.deleteById(id);
System.out.println("删除商品成功!");
return 200;
}
else
System.out.println("这个商品不存在!");
return 100;
}
//查
public Good queryGoodById(int id) {
if(goodMapper.isExist(id)) {
return goodMapper.queryById(id);
}
System.out.println("这个商品不存在!");
return null;
}
//查询所有
public List<Good> queryAll(){
return goodMapper.queryAll();
}
//改
public int updateGoodById(int id,Good good) throws ClassNotFoundException {
if(goodMapper.isExist(id)) {
goodMapper.updateById(id, good);
System.out.println("修改商品成功!");
return 200;
}
System.out.println("这个商品不存在!");
return 100;
}
//增
public boolean addGood(Good good) {
if(!goodMapper.isExist(good.getId())) {
System.out.println("添加商品成功!");
return goodMapper.add(good);
}else {
System.out.println("这个商品已存在!");
return false;
}
}
}
UserService业务层
同样可以搬前面已经写好的方法,稍加修改就OK了
package com.xmj.service;
import java.util.List;
import com.xmj.entity.DataVO;
import com.xmj.entity.User;
import com.xmj.mapper.UserMapper;
public class UserService {
private UserMapper userMapper = new UserMapper();
//分页查询
public DataVO<User> queryByPage(int currentPage,int pageSize){
DataVO<User> dataVO = new DataVO<User>();
List<User> goods = userMapper.queryByPage(currentPage,pageSize);
dataVO.setData(goods);
dataVO.setCode(0);
dataVO.setMsg("");
dataVO.setCount(goods.size());
return dataVO;
}
//查询当前页的数据集合
public int getTotalCount() {
return userMapper.getTotalCount();
}
//删
public int deleteUserById(int id) {
if(userMapper.isExist(id)) {
userMapper.deleteById(id);
System.out.println("删除用户成功!");
return 200;
}
else
System.out.println("这个用户不存在!");
return 100;
}
//查
public User queryUserById(int id) {
if(userMapper.isExist(id)) {
return userMapper.queryById(id);
}
System.out.println("这个用户不存在!");
return null;
}
//查询所有
public List<User> queryAll(){
return userMapper.queryAll();
}
//改
public int updateUserById(int id,User good) throws ClassNotFoundException {
if(userMapper.isExist(id)) {
userMapper.updateById(id, good);
System.out.println("修改用户成功!");
return 200;
}
System.out.println("这个用户不存在!");
return 100;
}
//增
public boolean addUser(User good) {
if(!userMapper.isExist(good.getId())) {
System.out.println("添加用户成功!");
return userMapper.add(good);
}else {
System.out.println("这个用户已存在!");
return false;
}
}
}
这是当前数据库User表的内容
这是当前数据库Good表的内容
我们建个Test测试类来看看吧
Test.java测试类
package com.xmj.test;
import java.util.Date;
import java.util.List;
import com.xmj.entity.Good;
import com.xmj.entity.User;
import com.xmj.service.GoodService;
import com.xmj.service.UserService;
public class Test {
public static void main(String[] args) throws Exception{
GoodService goodService = new GoodService();
UserService userService = new UserService();
Date time = new Date();
//通过id来查找
System.out.println(goodService.queryGoodById(111113));
System.out.println(userService.queryUserById(11112));
//添加
System.out.println(goodService.addGood(new Good("新商品",(float)2.5,43,43,"测试","测试")));
System.out.println(userService.addUser(new User("新用户","dsa",432,"333333",34,"dsasa",time,2323,"343333")));
//查询全部
List<Good> goods = goodService.queryAll();
for(Good good : goods){
System.out.println(good.getName());
}
List<User> users = userService.queryAll();
for(User user : users){
System.out.println(user.getName());
}
//通过id来修改
// System.out.println(userService.updateUserById(11112, new User("222222","dsa",432,"333333",34,"dsasa",time,2323,"343333")));
// System.out.println(goodService.updateGoodById(11112, new Good("测试",(float)222,43,43,"测试","测试")));
//通过id来删除
// System.out.println(goodService.deleteGoodById(111113));
// System.out.println(userService.deleteUserById(1111));
}
}