java操作数据库：增删改查

不多bb了直接上。

工具：myeclipse 2016，mysql 5.7

目的：java操作数据库增删改查商品信息

test数据库的goods表

gid主键，自增

1、实体类Goods：封装数据库数据（与数据库表中各字段相匹配的类）

// 实体类
public class Goods {
    private int gid;
    private String gname;
    private double gprice;
    private String gdate;
    //生成get、set方法
    public int getGid() {
        return gid;
    }
    public void setGid(int gid) {
        this.gid = gid;
    }
    public String getGname() {
        return gname;
    }
    public void setGname(String gname) {
        this.gname = gname;
    }
    public double getGprice() {
        return gprice;
    }
    public void setGprice(double gprice) {
        this.gprice = gprice;
    }
    public String getGdate() {
        return gdate;
    }
    public void setGdate(String gdate) {
        this.gdate = gdate;
    }
    //生成构造方法
    public Goods(int gid, String gname, double gprice, String gdate) {
        super();
        this.gid = gid;
        this.gname = gname;
        this.gprice = gprice;
        this.gdate = gdate;
    }
    //生成无参构造方法
    public Goods() {
        super();
    }
    
}

2、实现类GoodsDao（不带DBHelper）：操作数据库实现增删改查 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class GoodsDao {

    public static void main(String[] args) {
        GoodsDao dao = new GoodsDao();
//        dao.add();
        
//        dao.del();
        
//        dao.upd();
        Scanner input = new Scanner(System.in);
        
        System.out.println("请输入商品名称：");
        
        String name = input.next();
        
        System.out.println("请输入商品价格：");
        
        double price = input.nextDouble();
        
        dao.newAdd(name, price);
        
        
//        System.out.println("请输入最低价格：");
//        double price = input.nextDouble();
//        
//        dao.search(price);
//        
        System.out.println("已经成功完成操作！");
    }

    // 增数据
    public void add(){
        try{
            // 1.通过反射，加载驱动类到jvm
            Class.forName("com.mysql.jdbc.Driver");
            // 2.获取数据库连接对象
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true","root","");
            // 3.创建数据库操作对象
            Statement stmt = conn.createStatement();
            // 4.操作数据库
            stmt.execute("insert into goods(gname,gprice,gdate) values('ggg','2.33',now())");
            
            //5.关闭各个资源
            stmt.close();
            conn.close();
            
        } catch(Exception e){
            e.printStackTrace();
        }
    }
    //删数据
    public void del(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true","root","");
            
            Statement stmt = conn.createStatement();
            
            stmt.execute("delete from goods where gid=1");
            
            stmt.close();
            conn.close();
            
        } catch(Exception e){
            e.printStackTrace();
        }
    }
    //改数据
    public void upd(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true","root","");
            
            Statement stmt = conn.createStatement();
            stmt.execute("update goods set gname='ggg' where gid=1 ");
            
            stmt.close();
            conn.close();
            
        } catch(Exception e){
            e.printStackTrace();
        }
    }
    //查数据
    public void search(double price){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true","root","");
            
            Statement stmt = conn.createStatement();
            // 结果集对象
            ResultSet rs = stmt.executeQuery("select * from goods where gprice>"+price);
            
            while(rs.next()){
                System.out.println(rs.getString(1)+"#"+rs.getString("gname")+"#"+rs.getDouble("gprice")+"#"+rs.getString("gdate"));
            }
            
            rs.close();
            stmt.close();
            conn.close();
            
        } catch(Exception e){
            e.printStackTrace();
        }
    }
    
    //用拼字符串的方法增数据    
    public void newAdd(String gname, double gprice){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true","root","");
            
//            Statement stmt = conn.createStatement();
//            
//            stmt.execute("insert into goods(gname, gprice,gdate) values('"+gname+"','"+gprice+"',now())");
//            
//            stmt.close();
            //拼字符串更简单
            String sql = "insert into goods(gname, gprice, gdate) values(?,?,now())";
            
            PreparedStatement pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, gname);
            pstmt.setDouble(2, gprice);
            
            pstmt.execute();
            
            conn.close();
        } catch(Exception e){
            e.printStackTrace();
        }
    }
    
}

3、DBHelper类：解决上述代码操作数据库的重复工作

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * 获取数据库操作的连接对象
 * 关闭数据库操作的各种资源
 * @author 晏先政
 *
 */
public class DBHelper {
    private static final String className = "com.mysql.jdbc.Driver";
    private static final String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true";
    private static final String uname = "root";
    private static final String upass = "";
    
    /**
     * 获取数据库连接对象的方法
     */
    public static Connection getConn(){
        Connection conn = null;
        try{
            Class.forName(className);
            conn = DriverManager.getConnection(url,uname, upass);
        } catch(Exception e){
            e.printStackTrace();
        }
        
        return conn;
    }
    
    /**
     * 关闭数据库连接对象
     */
    public static void closeConn(Connection conn){
        try{
            if(conn!=null){
                conn.close();
            }
        } catch(Exception e){
            e.printStackTrace();
        }
    }
    
    /**
     * 关闭数据库操作对象
     */
    public static void closeStmt(Statement stmt){
        try{
            if(stmt!=null){
                stmt.close();
            }
        } catch(Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 关闭数据库操作对象
     */
    public static void closePstmt(PreparedStatement pstmt){
        try{
            if(pstmt!=null){
                pstmt.close();
            }
        } catch(Exception e){
            e.printStackTrace();
        }
    }
    
    /**
     * 关闭数据库操作对象
     */
    public static void closeRs(ResultSet rs){
        try{
            if(rs!=null){
                rs.close();
            }
        } catch(Exception e){
            e.printStackTrace();
        }
    }
}

4、实现类NewDao（带DBHelper）：操作数据库实现增删改查 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class NewDao {

    public static void main(String[] args) {
        NewDao nd = new NewDao();
        nd.show();
//        Scanner input = new Scanner(System.in);        
//改数据
//        System.out.println("请输入要修改的商品编号：");
//        int gid = input.nextInt();
//        System.out.println("请输入要修改的商品名称：");
//        String gname = input.next();
//        System.out.println("请输入要修改的商品价格：");
//        double gprice = input.nextDouble();
//        
//        nd.upd(gid, gname, gprice);
        
        
//查数据        
//        System.out.println("请输入最低价格：");
//        double mprice = input.nextDouble();
//        System.out.println("请输入最高价格：");
//        double xprice = input.nextDouble();
//        
//        nd.search(mprice, xprice);
        
    }    
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
//查数据    
    public void search(double minprice, double maxprice){
        try{
            conn = DBHelper.getConn();
            String sql = "select * from goods where gprice>=? and gprice<=?";
            // 预编译的对象
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setDouble(1, minprice);
            pstmt.setDouble(2, maxprice);
            
            rs = pstmt.executeQuery();
            while(rs.next()){
                System.out.println(rs.getString("gid")+"#"+rs.getString("gname")+"#"+rs.getString("gprice"));
            }
            
        } catch(Exception e){
            e.printStackTrace();
        } finally{
            DBHelper.closeRs(rs);
            DBHelper.closePstmt(pstmt);
            DBHelper.closeConn(conn);
        }
    }
    
//改数据
    public void upd(int gid , String gname, double gprice){
        try{
            conn = DBHelper.getConn();
            String sql = "update goods set gname=?, gprice=? where gid=?";
            
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, gname);
            pstmt.setDouble(2, gprice);
            pstmt.setInt(3, gid);
            
            pstmt.execute();
            
        } catch(Exception e){
            e.printStackTrace();
        } finally{
            DBHelper.closePstmt(pstmt);
            DBHelper.closeConn(conn);
        }
    }
    
//用集合展示数据    
    public List<Goods> getAllGoods(){
        List<Goods> list = new ArrayList<Goods>();
        try{
            conn = DBHelper.getConn();
            
            String sql = "select * from goods";
            
            pstmt = conn.prepareStatement(sql);
            
            rs = pstmt.executeQuery();
            
            while(rs.next()){
                Goods goods = new Goods(rs.getInt("gid"),rs.getString("gname"),rs.getDouble("gprice"),rs.getString("gdate"));
                
                list.add(goods);
            }
            
        } catch(Exception e){
            e.printStackTrace();
        } finally{
            DBHelper.closeRs(rs);
            DBHelper.closePstmt(pstmt);
            DBHelper.closeConn(conn);
        }
        return list;
    }
    
    public void show(){
        List<Goods> list = getAllGoods();
        
        for(int i=0;i<list.size();i++){
            System.out.println(list.get(i).getGid()+"#"+list.get(i).getGname()+"#"+list.get(i).getGprice());
        }
    }
    
}

 

转载于:https://www.cnblogs.com/Vito-Yan/p/6815742.html