Java通过JDBC实现对数据库的增删查改（预编译方式）

本次试验用的是SQLserver2012，其他数据库只需要更改连接部分，模板仅供参考

• 加载jdbc驱动，实现数据库连接

package database;
/***
 * 数据库工具类
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtils {
    // 获取数据库连接
    public static Connection getConnection() {
        Connection con = null;
        //定义连接相关属性
        final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        final String URL = "jdbc:sqlserver://localhost:1433;databaseName=mina";
        final String USER = "sa";
        final String PWD = "154451";
        
        try {
            
            Class.forName(DRIVER);
            con = DriverManager.getConnection(URL, USER,PWD);
            
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }
}

• 实现与数据库中表字段相同的类

package database;
/***
 * 在线设备信息
 * @author liaot
 *
 */
public class OnlineDevice {
    private String imei;
    private String state;
    
    public OnlineDevice(String imei, String state) {
        super();
        this.imei = imei;
        this.state = state;
    }

    public String getImei() {
        return imei;
    }

    public void setImei(String imei) {
        this.imei = imei;
    }

    public String getState() {
        return state;
    }

    public void setState(String state) {
        this.state = state;
    }
    
}

• 实现对应数据库操作类

package database;

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

/***
 * 数据库操作类
 * 
 * @author liaot
 *
 */
public class OnlineDeviceDAO {
    // 数据插入
    public void insert(OnlineDevice od) {
        String sql = "insert into t_online values(?,?);";
        Connection con = DBUtils.getConnection();

        try {
            // 预编译
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, od.getImei());
            ps.setString(2, od.getState());
            // 执行
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据IEMI查询
    public List<OnlineDevice> selectByIemi(String imei) {
        List<OnlineDevice> ls = new ArrayList<>();
        String sql = "select * from t_online where imei = ?;";
        Connection con = DBUtils.getConnection();
        try {
            // 预编译
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, imei);
            // 执行
            ResultSet rs = ps.executeQuery();
            // 遍历将结果集 加到list中
            while (rs.next()) {
                OnlineDevice od = new OnlineDevice(rs.getString(1), rs.getString(2));
                ls.add(od);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ls;
    }

    // 查询所以数据
    public List<OnlineDevice> selectAll() {
        List<OnlineDevice> ls = new ArrayList<>();
        String sql = "select * from t_online";
        Connection con = DBUtils.getConnection();
        try {
            // 预编译
            PreparedStatement ps = con.prepareStatement(sql);
            // 执行
            ResultSet rs = ps.executeQuery();
            // 遍历将结果集 加到list中
            while (rs.next()) {
                OnlineDevice od = new OnlineDevice(rs.getString(1), rs.getString(2));
                ls.add(od);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ls;
    }

    // 数据修改
    public void update(OnlineDevice od) {
        String sql = "update t_online set state=? where imei = ?;";
        Connection con = DBUtils.getConnection();

        try {
            // 预编译
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, od.getState());
            ps.setString(2, od.getImei());
            // 执行
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 数据删除
    public void delete(OnlineDevice od) {
        String sql = "delete from t_online where imei = ?;";
        Connection con = DBUtils.getConnection();

        try {
            // 预编译
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, od.getImei());
            // 执行
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

• 数据库表结构：