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语句的具体内容暂时不设置,而是之后再设置