jdk8+jdbc连接数据库
目录
前言 什么是JDBC
维基百科的简介:
Java 数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。它JDBC是面向关系型数据库的。
简单地说,就是用于执行SQL语句的一类Java API,通过JDBC使得我们可以直接使用Java编程来对关系数据库进行操作。通过封装,可以使开发人员使用纯Java API完成SQL的执行。
(一)准备工作
1.创建数据库
DROP TABLE IF EXISTS `t_goods`;
CREATE TABLE `t_goods` (
`id` bigint(20) NOT NULL COMMENT '主键',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '商品名称',
`price` double(10,0) DEFAULT NULL COMMENT '价钱',
`number` int(11) DEFAULT NULL COMMENT '数量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_r_goods_salesperson`;
CREATE TABLE `t_r_goods_salesperson` (
`id` bigint(20) NOT NULL COMMENT '主键',
`goods_id` bigint(20) NOT NULL COMMENT '商品表外键',
`salesperosn_id` bigint(20) NOT NULL COMMENT '销售员外键',
`number` int(10) NOT NULL COMMENT '销售量',
`sales_time` datetime NOT NULL COMMENT '商品卖出时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_salesperson`;
CREATE TABLE `t_salesperson` (
`id` bigint(20) NOT NULL COMMENT '主键',
`name` varchar(4) DEFAULT NULL COMMENT '销售员姓名',
`password` varchar(30) DEFAULT NULL COMMENT '销售员密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.导包
![enter description here][1]
3.实体
public class Goods {
private Integer id ;
private String name ;
private Double price ;
private Integer number ;
....
省略构造器,setter,getter
}
public class GoodsSalesperson {
private Integer id ;
private Integer goodsId ;
private Integer salespersonId ;
private Integer number ;
private Date salesTime ;
....
省略构造器,setter,getter
}
public class Salesperson {
private Integer id ;
private String name ;
private String password ;
....
省略构造器,setter,getter
}
4.建立连接
public class DBUtils {
public static Connection getConnetction() {
Connection conn = null;
String user = "root";
String passwd = "htbuy@2016";
String url = "jdbc:mariadb://192.168.6.101:3306/test_shopping";
try {
//Class.forName("oracle.jdbc.driver.OracleDriver");
Class.forName("org.mariadb.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(url,user,passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeResource(PreparedStatement pstmt, Connection conn) {
try {
if (pstmt != null) {
pstmt.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closeResource(PreparedStatement pstmt, ResultSet rs, Connection conn) {
try {
if (pstmt != null) {
pstmt.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if (rs != null ) {
rs.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5.insert
/**
* @description <p>添加商品到数据库t_goods表</p>
* @author heshiyuan
* @date 2017/7/20 11:39
* @param goods 商品对象
*/
public boolean addGoods(Goods goods) {
boolean bool = false;
conn = DBUtils.getConnetction();
String sql = "INSERT INTO t_GOODS(id,NAME,PRICE,number) VALUES(?,?,?,?)";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, goods.getId());
pstmt.setString(2, goods.getName());
pstmt.setDouble(3, goods.getPrice());
pstmt.setInt(4, goods.getNumber());
int rs = pstmt.executeUpdate();
if (rs > 0) {
bool = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeResource(pstmt, conn);
}
return bool;
}
6.update
/** @description <p>更改商品信息到数据库t_goods表</p>
* @author heshiyuan
* @param key 选择要更改商品信息
* @param goods 商品对象
* @date 2017/7/20 11:39
*/
public boolean updateGoods(int key, Goods goods) {
boolean bool = false;
conn = DBUtils.getConnetction();
switch (key) {
case 1: // key=1,更改商品名称
String sqlName = "UPDATE t_goods SET NAME=? WHERE ID=?";
try {
pstmt = conn.prepareStatement(sqlName);
pstmt.setString(1, goods.getName());
pstmt.setLong(2, goods.getId());
int rs = pstmt.executeUpdate();
if (rs > 0) {
bool = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeResource(pstmt, conn);
}
break;
case 2: // key=2,更改商品价格
String sqlPrice = "UPDATE t_goods SET PRICE=? WHERE ID=?";
try {
conn = DBUtils.getConnetction();
pstmt = conn.prepareStatement(sqlPrice);
pstmt.setDouble(1, goods.getPrice());
pstmt.setLong(2, goods.getId());
int rs = pstmt.executeUpdate();
if (rs > 0) {
bool = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeResource(pstmt, conn);
}
break;
case 3: // key=3,更改商品数量
String sqlNum = "UPDATE t_goods SET number=? WHERE ID=?";
try {
conn = DBUtils.getConnetction();
pstmt = conn.prepareStatement(sqlNum);
pstmt.setInt(1, goods.getNumber());
pstmt.setLong(2, goods.getId());
int rs = pstmt.executeUpdate();
if (rs > 0) {
bool = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeResource(pstmt, conn);
}
break;
default:
break;
}
return bool;
}
7.select
public List<Map<String,Object>> dailyGsales() {
List<Map<String,Object>> returnMapList = new ArrayList<>();
conn = DBUtils.getConnetction();
String sql = "SELECT" +
" gs.sales_time as salesTime," +
" gs.id as id," +
" g.`name` as goodsName," +
" s.`name` as salesName," +
" g.price as price," +
" gs.number as count," +
" g.price * g.number as total" +
" FROM" +
" t_r_goods_salesperson gs left join t_salesperson s on gs.salesperosn_id = s.id" +
" left join t_goods g on gs.goods_id = g.id" +
";";
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
Map<String,Object> returnMap = new HashMap<>() ;
returnMap.put("saleTime",rs.getTimestamp(1));
returnMap.put("id",rs.getInt(2));
returnMap.put("goodsName",rs.getString(3));
returnMap.put("salesName",rs.getString(4));
returnMap.put("price",rs.getDouble(5));
returnMap.put("count",rs.getInt(6));
returnMap.put("total",rs.getDouble(7));
returnMapList.add(returnMap) ;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeResource(pstmt, rs, conn);
}
return returnMapList;
}
8.delete
/**
* @description <p>从数据库goods表中-刪除商品</p>
* @author heshiyuan
* @date 2017/7/20 11:38
*/
public boolean deleteGoods(int id) {
boolean bool = false;
String sql = "DELETE FROM t_GOODS WHERE ID=?";
try {
conn = DBUtils.getConnetction();
pstmt = conn.prepareStatement(sql);
pstmt.setLong(1,id);
int rs = pstmt.executeUpdate();
if (rs > 0) {
bool = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeResource(pstmt, conn);
}
return bool;
}
(二)项目演示
1.商城主页面
2.商品列表
3.售出列表
(三)篇后感
目前的项目功能并不丰富,简单的jdbc增删改查,此项目会继续维护,
项目源码托管在GitHub:https://github.com/shiyuan2he/framework.git
后续会增加jdbc增删改查的封装(思路是java 反射),添加jdbc事务支持,aop切面拦截
持续更新中,敬请期待