JDBC(java database connectivity)
[toc]
JDBC
java database connectivity即Java数据库连接，它是一种可以执行SQL语句的Java API。程序可通过JDBC API连接到关系数据库，并使用结构化查询语言（SQL，数据库标准的查询语言）来完成对数据库的查询、更新
Java 面对对象语言
语言不通，需要翻译（Java支持标准 数据库当做翻译）
Mysql 结构化查询语言
连接数据库
连接数据库的步骤
- jar加入工程下
- jar解压到本工程下 build path
- 上两步在同一个程序执行一次
- 加载驱动 Class.forName（"....Driver"）
- 获取连接对象
String url = "jdbc:mysql://ip:3306//数据库名";
String user = "用户名";
String password = "密码";
Connection conn = DriverManage.getConnection(url,user,password);
5.获取执行语句的sql对象
//sql语句可以拼接，Statement出现Bug
Statement st = conn.ceartStatement();
//PreparedStatement继承了Statement的接口，且PreparedStatement可以使用占位符，是预编译的，批处理比Statement效率高
PreparedStatement pstm = conn.prepareStatement();
6.执行Sql语句
//增删改返回值
int row = psmt.executeUodate(Sql)
//查询的返回值
ResultSet rs = psmt.executeQuery(Sql);
//遍历rs对象
while(rs.next()){
// XX 变量 = rs.getXX("字段名")； 获取该记录指定记录
}
JDBC的应用例子
超市管理系统实例
相关数据库的搭建
create database supermakemanage
use supermakemanage
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`store` int(11) DEFAULT NULL,
`price` decimal(7,2) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of product
-- ----------------------------
-- ----------------------------
-- Table structure for sale
-- ----------------------------
DROP TABLE IF EXISTS `sale`;
CREATE TABLE `sale` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
`number` int(11) DEFAULT NULL,
`createtime` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sale
-- ----------------------------
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`role` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'admin', 'admin', '1');
INSERT INTO `user` VALUES ('2', 'zs', 'zs', '0');
INSERT INTO `user` VALUES ('3', 'lisi', 'lisi', '0');
java中操作类的创建（一张表对应一个操作类与一个实体类）
操作类的父类创建
BaseDao:抽象Dao操作类的父类（公共代码）
package com.huawei.lb.Dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//超市管理系统的操作父类
public class BaseDao {
protected String driver="com.mysql.jdbc.Driver";
protected Connection conn= null;
protected PreparedStatement psmt = null;
protected String url = "jdbc:mysql://localhost:3306/supermarkemanage";
protected String user = "root";
protected String password = "123456";
protected ResultSet rs = null;
//关闭所有资源
public void colseAll() {
try {
if(psmt!=null) {
psmt.close();
}
if(rs!=null) {
rs.close();
}
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 增删改的源码
* @param sql sql语句
* @param pratm
*/
public void update(String sql,Object...pratm) {
try {
getconn();
psmt = conn.prepareStatement(sql);
for(int i=0;i<pratm.length;i++) {
psmt.setObject(i+1, pratm[i]);
}
psmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}finally {
colseAll();
}
}
//获取连接对象
public void getconn() throws Exception{
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
}
}
商品表操作类创建
操作类：ProductDao
package com.huawei.lb.Dao;
import java.util.ArrayList;
import java.util.List;
import com.huawei.lb.bean.Product;
/**
* 商品的操作类
* @author Administrator
*
*/
public class ProductDao extends BaseDao {
/**
* 添加商品
* @param name 商品名称
* @param price 商品价格
* @param uid 操作人员名字
*/
public void addProduct(String name,Double price,int uid) {
String Sql = "insert into Product (name,price,uid)values(?,?,?)";
update(Sql, name,price,uid);
}
/**
* 商品出入库操作
* @param id 商品编号
* @param number 商品出入数量
*/
public void updateStore(int id,int number) {
String Sql = "update Product set store=ifnull(store,0)+? where id=?";
update(Sql,number,id);
}
/**
* 查询所有商品信息
* @return
*/
public List<Product> selectAll(){
List<Product> list = new ArrayList<Product>();
try {
getconn();
psmt = conn.prepareStatement("select * from Product");
rs = psmt.executeQuery();
while(rs.next()) {
Product p = new Product();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setStore(rs.getInt("store"));
p.setPrice(rs.getDouble("price"));
p.setUid(rs.getInt("uid"));
list.add(p);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
colseAll();
}
return list;
}
/**
* 根据商品名称查询商品信息
* @param name 商品名称
* @return
*/
public Product selectStore(String name){
Product p = null;
try {
getconn();
psmt = conn.prepareStatement("select * from Product where name=?");
psmt.setString(1, name);
rs = psmt.executeQuery();
while(rs.next()) {
p = new Product();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setStore(rs.getInt("store"));
p.setPrice(rs.getDouble("price"));
p.setUid(rs.getInt("uid"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
colseAll();
}
return p;
}
/**
* 根据商品编号查询商品信息
* @param pid 商品编号
* @return
*/
public Product selectStote(int pid){
Product p = null;
try {
getconn();
psmt = conn.prepareStatement("select * from Product where id=?");
psmt.setInt(1, pid);
rs = psmt.executeQuery();
while(rs.next()) {
p = new Product();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setStore(rs.getInt("store"));
p.setPrice(rs.getDouble("price"));
p.setUid(rs.getInt("uid"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
colseAll();
}
return p;
}
}
##### 销售表操作类创建
** 操作类：SaleDao**
package com.huawei.lb.Dao;
import java.util.ArrayList;
import java.util.List;
import com.huawei.lb.bean.Product;
import com.huawei.lb.bean.Sale;
/**
* 销售表操作类
* @author Administrator
*
*/
public class SaleDao extends BaseDao {
/**
* 添加销售表
* @param uid 操作人员编号
* @param pid 商品编号
* @param num 商品数量
*/
public void addSale(int uid,int pid,int num) {
ProductDao pd = new ProductDao();
Product p = pd.selectStote(pid);
if(p.getStore()>=num) {
String Sql = "insert into sale(uid,pid,number,createtime)values(?,?,?,now())";
update(Sql, uid, pid, num);
pd.updateStore(pid,-num);
}else
{
throw new RuntimeException("库存不足");
}
}
/**
* 查询销售情况表
* @return
*/
public List<Sale> find(){
List<Sale> list = new ArrayList<Sale>();
try {
getconn();
String sql = "SELECT p.id pid,p.name pname,price,number,(number*price) sumsale,u.name uname,createtime FROM user u,\r\n" +
"sale s,product p WHERE u.id=s.uid and s.pid=p.id ";
psmt = conn.prepareStatement(sql);
rs = psmt.executeQuery();
while(rs.next()) {
Sale s = new Sale();
s.setPid(rs.getInt("pid"));
s.setNum(rs.getInt("number"));
s.setUname(rs.getString("uname"));
s.setPname(rs.getString("pname"));
s.setSumSale(rs.getDouble("sumsale"));
s.setPrice(rs.getDouble("price"));
s.setCreatetime(rs.getDate("createtime"));
list.add(s);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
colseAll();
}
return list;
}
}
用户表操作类创建
操作类：UserDao
package com.huawei.lb.Dao;
import java.util.ArrayList;
import java.util.List;
import com.huawei.lb.bean.User;
/**
* User的操作类
* @author Administrator
*
*/
public class UserDao extends BaseDao {
/**
* 用户登录模块
* @param name 用户名
* @param password 密码
* @return
*/
public User login(String name,String password ) {
User user = null;
try {
getconn();
psmt = conn.prepareStatement("select * from user where name=? and password=?");
psmt.setString(1,name);
psmt.setString(2,password);
rs = psmt.executeQuery();
while(rs.next()) {
user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setRole(rs.getInt("role"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
colseAll();
}
return user;
}
/**
* 查询所有普通用户信息
* @return
*/
public List<User> selectAll(){
List<User> list = new ArrayList<>();
try {
getconn();
psmt = conn.prepareStatement("select * from user where role=0");
rs = psmt.executeQuery();
while(rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setRole(rs.getInt("role"));
list.add(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
colseAll();
}
return list;
}
/**
* 删除指定用户编号的所有信息
* @param id 用户编号
*/
public void deleteUser(int id) {
String Sql = "delete from User where id=?";
update(Sql, id);
}
}
Java实体类的创建
商品表的实体类创建
bean:Product
package com.huawei.lb.bean;
/**
* 商品实体类
* @author Administrator
*
*/
public class Product {
private int id;
private String name;
private int store;
private Double price;
private int uid;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getStore() {
return store;
}
public void setStore(int store) {
this.store = store;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
}
销售表的实体类创建
bean：Sale
package com.huawei.lb.bean;
import java.util.Date;
/**
* 销售表的实体类
*
* @author Administrator
*
*/
public class Sale {
private int pid;
private String pname;
private double price;
private int num;
private double sumSale;
private String uname;
private Date createtime;
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public double getSumSale() {
return sumSale;
}
public void setSumSale(double sumSale) {
this.sumSale = sumSale;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
}
用户表的实体类创建
bean：User
package com.huawei.lb.bean;
/**
* User 的实体类
* @author Administrator
*
*/
public class User {
private int id;
private String name;
private String password;
private int role;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getRole() {
return role;
}
public void setRole(int role) {
this.role = role;
}
}
测试类创建
package com.huawei.lb.test;
import java.util.List;
import java.util.Scanner;
import com.huawei.lb.Dao.ProductDao;
import com.huawei.lb.Dao.SaleDao;
import com.huawei.lb.Dao.UserDao;
import com.huawei.lb.bean.Product;
import com.huawei.lb.bean.Sale;
import com.huawei.lb.bean.User;
public class Test {
public static void main(String[] args) {
@SuppressWarnings("resource")
Scanner s = new Scanner(System.in);
System.out.println("请输入用户名：");
String name = s.next();
System.out.println("请输入密码：");
String password = s.next();
UserDao user = new UserDao();
ProductDao pt = new ProductDao();
SaleDao sd = new SaleDao();
User use = user.login(name, password);
if (use == null) {
System.out.println("用户名或密码错误");
} else {
if (use.getRole() == 1) {
//管理员的操作选择
System.out.println("1.查询所有普通用户\t 2.删除用户 \t 3.查看商品信息\t 4.录入商品信息\t 5.商品入库\t 6.商品出库\t 7.查询销售情况表");
int in = s.nextInt();
if (in == 1) {
List<User> list = user.selectAll();
for (User o : list) {
// 显示用户编号，用户姓名，密码，用户等级
System.out.println(o.getId() + "\t" + o.getName() + "\t" + o.getPassword() + "\t" + o.getRole());
}
} else if (in == 2) {
//删除指定编号的用户
System.out.println("请输入要删除的用户ID");
int i = s.nextInt();
user.deleteUser(i);
}else if(in == 3) {
// 显示商品编号，商品名称，商品库存，商品价格，用户编号
List<Product> list = pt.selectAll();
for (Product o : list) {
System.out.println(o.getId() + "\t" + o.getName() + "\t" + o.getStore()
+ "\t" + o.getPrice()+"\t"+ o.getUid());
}
}else if(in == 4) {
// 添加商品的名称，商品的价格
System.out.println("请输入商品的名称");
String name1 = s.next();
System.out.println("请输入商品的价格");
double price = s.nextDouble();
pt.addProduct(name1,price,use.getId());
}else if(in == 5 || in == 6) {
//根据商品编号对商品进行出入库处理
System.out.println("请输入商品的id");
int id = s.nextInt();
System.out.println("请输入商品的数量");
int number = s.nextInt();
if(in ==5)
pt.updateStore(id, number);
else
pt.updateStore(id, -number);
}else if(in == 7) {
// 查询商品的销售情况
List<Sale> list = sd.find();
for (Sale o : list) {
System.out.println(o.getPid() + "\t" + o.getPname() + "\t" + o.getPrice()+""+o.getNum()
+ "\t" + o.getSumSale()+"\t"+ o.getUname()+"\t"+o.getCreatetime());
}
}
}else {
// 普通用户的操作选择
System.out.println("1.查询所有商品\t 2.查看指定商品 \t 3.销售商品\t ");
int in = s.nextInt();
if(in == 1) {
List<Product> list = pt.selectAll();
for (Product o : list) {
System.out.println(o.getId() + "\t" + o.getName() + "\t" + o.getStore()
+ "\t" + o.getPrice());
}
}
else if(in == 2) {
//根据商品名称查询指定商品信息
System.out.println("请输入商品名称");
String name2 = s.next();
Product ps = pt.selectStore(name2);
if(ps == null) {
System.out.println("该商品不存在");
}else {
System.out.println(ps.getId()+"\t"+ps.getName()+"\t"+ps.getPrice()+"\t"+ps.getStore());
}
}else{
//根据商品编号，销售商品
System.out.println("请输入商品id");
int pid = s.nextInt();
System.out.println("请输入商品数量");
int num = s.nextInt();
sd.addSale(use.getId(), pid, num);
}
}
}
}
}
