mysql操作

1、conn需要导入的包：mysql-connector-java-5.1.39-bin.jar

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MysqlInit {

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://10.0.1.66:3306/database";


    static final String USER = "root";
    static final String PASS = "xxxx";

    private Connection conn;

    public MysqlInit(){
        conn =null;
    }

    public Connection openConn(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }

    public boolean closeConn(){
        boolean result = false;
        try {
            this.conn.close();
            result = true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }


}

2、查询

public List<userMap> searchAll(){
        List<userMap> ulist = new ArrayList<userMap>();
        userMap  u1 = null;
        Connection conn=super.openConn();
        String sql="select * from admin";
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                u1 = new userMap();
                u1.setId(rs.getInt("id"));
                u1.setName(rs.getString("name"));
                u1.setPassword(rs.getString("password"));
                ulist.add(u1);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return ulist;
    }

2.1 条件查询

public List<checkinoutMap> SearchCurDate(String front,String back){
        List<checkinoutMap> people = new ArrayList<checkinoutMap>();  
        String sql = "select * from CHECKINOUT where CHECKTIME between CONVERT(datetime,?) and CONVERT(datetime,?) order by  CHECKTIME desc";

        try {
            pstm = this.conn.prepareStatement(sql);
            pstm.setString(1, front + " 00:00:00");
            pstm.setString(2, back + " 23:59:59");
            // 执行SQL语句
            ResultSet rs = pstm.executeQuery();

            checkinoutMap p =null;
            while(rs.next()){//如果对象中有数据，就会循环打印出来  
                p = new checkinoutMap();  
                p.setUSERID(rs.getInt("USERID"));
                p.setCHECKTIME(rs.getString("CHECKTIME"));
                p.setCHECKTYPE(rs.getString("CHECKTYPE"));
                p.setVERIFYCODE(rs.getBoolean("VERIFYCODE"));
                p.setSENSORID(rs.getInt("SENSORID"));
                p.setMemoinfo(rs.getString("Memoinfo"));
                p.setWorkCode(rs.getInt("Memoinfo"));
                p.setSn(rs.getInt("sn"));
                p.setUserExtFmt(rs.getInt("UserExtFmt"));
                people.add(p);  
            }  

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return people;
    }

3、增加

//insert
    public boolean add(userMap u1){
        boolean result = false;
        Connection conn=super.openConn();

        String sql = "insert into admin (name,password) values(?,?)";
        try {
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, u1.getName());
            pstmt.setString(2, u1.getPassword());

            pstmt.executeUpdate();
            result = true;

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        };
        super.closeConn();
        return result;
    }

4、修改（结构和增加类似）

public boolean update(userMap u1){
        boolean result = false;
        Connection conn=super.openConn();
        String sql = "update admin set name=?,password=? where id=?";
        try {
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, u1.getName());
            pstmt.setString(2, u1.getPassword());
            pstmt.setInt(3, u1.getId());
            pstmt.executeUpdate();
            result = true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        };
        return result;
    }

5、删除，结构和增加类似，使用PreparedStatement pstmt.executeUpdate