jdbc工具类

jdbc工具类

package com.stu.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DbConfig {

    /**
     * 数据库连接类
     */
    private static String username=null;
    private static String password=null;
    private static  String driver=null;
    private static String url=null;
    static{
        Properties ps=new Properties();
        try {
            ps.load(DbConfig.class.getResourceAsStream("db.properties"));
            driver=ps.getProperty("driver");
            url=ps.getProperty("url");
            username=ps.getProperty("username");
            password=ps.getProperty("password");
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public   Connection getCon() throws Exception{
        Class.forName(driver);
        Connection con=DriverManager.getConnection(url, username, password);
        return con;
    }
//    public static void Close(Connection con)throws Exception{
//        if(con!=null){
//            con.close();
//        }
//    }
//    public void closeCon(Connection con) {
//        // TODO Auto-generated method stub
//        if(con!=null){
//            try {
//                con.close();
//            } catch (SQLException e) {
//                // TODO Auto-generated catch block
//                e.printStackTrace();
//            }
//        }
//    }
//    public   void main(String[] args) {
//        try {
//            System.out.println(getCon());
//        } catch (Exception e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }
//    }

}

增

//增加
    @Override
    public int AddDbsupermarket(DbSupermarket dbSupermarket) {
        try {
            String sql = "insert  into  dbSupermarket(GNAME,GPRICE) values(?,?)";
            Statement stmt = null;
            ResultSet rs = null;
            DbConfig dbConfig = new DbConfig();
            Connection conn = dbConfig.getCon();
            PreparedStatement ps= conn.prepareStatement(sql);
            //ps.setInt(1,dbSupermarket.getGId());
            ps.setString(1,dbSupermarket.getGNAME());
            ps.setFloat(2,dbSupermarket.getGPRICE());
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

删

//删除
    @Override
    public int deleteDbsupermarket(int gid) {
        try {
            DbConfig dbConfig = new DbConfig();
            Connection conn = dbConfig.getCon();
            String sql = "delete from dbSupermarket where GID = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,gid);
            ps.executeUpdate();

            ps.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;


    }

改

    //改
    @Override
    public int updateDbsupermarket(DbSupermarket dbSupermarket) {
        try {
            DbConfig dbConfig = new DbConfig();
            Connection conn = dbConfig.getCon();
            String sql = "update dbSupermarket set GNAME = ?,GPRICE = ? where GID = ?";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1,dbSupermarket.getGNAME());
            ps.setFloat(2,dbSupermarket.getGPRICE());
            ps.setInt(3,dbSupermarket.getGId());

            ps.executeUpdate();
            ps.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

查

 public List<DbSupermarket> findAll() {
        //DbSupermarket dd=new DbSupermarket();
        List<DbSupermarket > dbSupermarket= new ArrayList<DbSupermarket>();


        try {
            String sql = "SELECT * FROM dbSupermarket";
            PreparedStatement ps = null;
            Statement stmt = null;
            ResultSet rs = null;
            DbConfig dbConfig = new DbConfig();
            Connection conn = dbConfig.getCon();
            //stmt = conn.createStatement();
             PreparedStatement ps =conn.prepareStatement(sql);
            rs = ps.executeQuery(sql);
           // System.out.println(rs.getInt("GID"));
            while (rs.next()) {
                if(rs!=null) {
                    DbSupermarket dd = new DbSupermarket();
                    //System.out.println(rs.getInt("GID"));
                    dd.setGId(rs.getInt("GID"));
                    dd.setGNAME(rs.getString("GNAME"));
                    dd.setGPRICE(rs.getFloat("GPRICE"));
                    dbSupermarket.add(dd);
                }
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dbSupermarket;
    }

总结

// 1.注册驱动
		Class.forName("com.mysql.jdbc.Driver");
		// 2.获取连接对象
		String url = "jdbc:mysql://39.106.200.227:3306/test?characterEncoding=utf-8";
		Connection conn = DriverManager.getConnection(url, "root", "root");
		String sql="select * from sort where like ? or password "
//以上封装成一个方法


PreparedStatement ps =conn.prepareStatement(sql);
ps.setString(1,"")//设置第一个?,模糊查询使用字符串拼接   %+String+%
ReSulSet rs =ps.executeQuery();//实例化ReSulSet对象
rs.next();//指针下移
rs.getString("");//对应什么字段传什么字符串

//关闭连接
rs.close();
ps.close();
conn.close();

//在增删改中ps调用executeUpdate();结果返回更新的记录数
//执行查询数据库操作，ps调用executeQuery();返回一个结果集对象，采用rs接，

注意jdbc在查询操作中，因为是讲数据表中的全部查询结果保存在ResultSet对象中，实际上也就是保存在内存中，所以如果查询出来的数据总量过大，系统将会出现问题

补充

prepareStatement是Statement的子接口，属于预处理操作，和之前操作Statement不同的是，prepareStatement在操作时，是先在数据表之中准备好了一条sql语句，电脑上此sql语句的具体内容暂时不设置，而是之后再设置