java mysql 预编译_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 selectByIemi(String imei) {

List 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 selectAll() {

List 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();

}

}

}

数据库表结构：

这里写图片描述