JDBC(java database connectivity)

JDBC

java database connectivity即Java数据库连接，它是一种可以执行SQL语句的Java API。程序可通过JDBC API连接到关系数据库，并使用结构化查询语言（SQL，数据库标准的查询语言）来完成对数据库的查询、更新

Java 面对对象语言

语言不通，需要翻译（Java支持标准 数据库当做翻译）

Mysql 结构化查询语言

连接数据库

连接数据库的步骤

1. jar加入工程下
2. jar解压到本工程下 build path
   1. 上两步在同一个程序执行一次
3. 加载驱动 Class.forName（"....Driver"）
4. 获取连接对象

     String url = "jdbc:mysql://ip:3306//数据库名";
     String user = "用户名";
     String password = "密码";
     Connection conn = DriverManage.getConnection(url,user,password);

5.获取执行语句的sql对象
java //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);????
????????????????}
????????????????
????????????}
????????}
????}
}

JDBC(java database connectivity)

原文：https://www.cnblogs.com/DT-Demo/p/11279595.html