不多bb了直接上。
工具：myeclipse 2016，mysql 5.7
目的：java操作数据库增删改查商品信息
test数据库的goods表
gid主键，自增
1、实体类Goods：封装数据库数据（与数据库表中各字段相匹配的类）
// 实体类
public class Goods {
private int gid;
private String gname;
private double gprice;
private String gdate;
//生成get、set方法
public int getGid() {
return gid;
}
public void setGid(int gid) {
this.gid = gid;
}
public String getGname() {
return gname;
}
public void setGname(String gname) {
this.gname = gname;
}
public double getGprice() {
return gprice;
}
public void setGprice(double gprice) {
this.gprice = gprice;
}
public String getGdate() {
return gdate;
}
public void setGdate(String gdate) {
this.gdate = gdate;
}
//生成构造方法
public Goods(int gid, String gname, double gprice, String gdate) {
super();
this.gid = gid;
this.gname = gname;
this.gprice = gprice;
this.gdate = gdate;
}
//生成无参构造方法
public Goods() {
super();
} }
2、实现类GoodsDao（不带DBHelper）：操作数据库实现增删改查
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner; public class GoodsDao { public static void main(String[] args) {
GoodsDao dao = new GoodsDao();
// dao.add(); // dao.del(); // dao.upd();
Scanner input = new Scanner(System.in); System.out.println("请输入商品名称："); String name = input.next(); System.out.println("请输入商品价格："); double price = input.nextDouble(); dao.newAdd(name, price); // System.out.println("请输入最低价格：");
// double price = input.nextDouble();
//
// dao.search(price);
//
System.out.println("已经成功完成操作！");
} // 增数据
public void add(){
try{
// 1.通过反射，加载驱动类到jvm
Class.forName("com.mysql.jdbc.Driver");
// 2.获取数据库连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true","root","");
// 3.创建数据库操作对象
Statement stmt = conn.createStatement();
// 4.操作数据库
stmt.execute("insert into goods(gname,gprice,gdate) values('ggg','2.33',now())"); //5.关闭各个资源
stmt.close();
conn.close(); } catch(Exception e){
e.printStackTrace();
}
}
//删数据
public void del(){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true","root",""); Statement stmt = conn.createStatement(); stmt.execute("delete from goods where gid=1"); stmt.close();
conn.close(); } catch(Exception e){
e.printStackTrace();
}
}
//改数据
public void upd(){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true","root",""); Statement stmt = conn.createStatement();
stmt.execute("update goods set gname='ggg' where gid=1 "); stmt.close();
conn.close(); } catch(Exception e){
e.printStackTrace();
}
}
//查数据
public void search(double price){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true","root",""); Statement stmt = conn.createStatement();
// 结果集对象
ResultSet rs = stmt.executeQuery("select * from goods where gprice>"+price); while(rs.next()){
System.out.println(rs.getString(1)+"#"+rs.getString("gname")+"#"+rs.getDouble("gprice")+"#"+rs.getString("gdate"));
} rs.close();
stmt.close();
conn.close(); } catch(Exception e){
e.printStackTrace();
}
} //用拼字符串的方法增数据
public void newAdd(String gname, double gprice){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true","root",""); // Statement stmt = conn.createStatement();
//
// stmt.execute("insert into goods(gname, gprice,gdate) values('"+gname+"','"+gprice+"',now())");
//
// stmt.close();
//拼字符串更简单
String sql = "insert into goods(gname, gprice, gdate) values(?,?,now())"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, gname);
pstmt.setDouble(2, gprice); pstmt.execute(); conn.close();
} catch(Exception e){
e.printStackTrace();
}
} }
3、DBHelper类：解决上述代码操作数据库的重复工作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement; /**
* 获取数据库操作的连接对象
* 关闭数据库操作的各种资源
* @author 晏先政
*
*/
public class DBHelper {
private static final String className = "com.mysql.jdbc.Driver";
private static final String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true";
private static final String uname = "root";
private static final String upass = ""; /**
* 获取数据库连接对象的方法
*/
public static Connection getConn(){
Connection conn = null;
try{
Class.forName(className);
conn = DriverManager.getConnection(url,uname, upass);
} catch(Exception e){
e.printStackTrace();
} return conn;
} /**
* 关闭数据库连接对象
*/
public static void closeConn(Connection conn){
try{
if(conn!=null){
conn.close();
}
} catch(Exception e){
e.printStackTrace();
}
} /**
* 关闭数据库操作对象
*/
public static void closeStmt(Statement stmt){
try{
if(stmt!=null){
stmt.close();
}
} catch(Exception e){
e.printStackTrace();
}
} /**
* 关闭数据库操作对象
*/
public static void closePstmt(PreparedStatement pstmt){
try{
if(pstmt!=null){
pstmt.close();
}
} catch(Exception e){
e.printStackTrace();
}
} /**
* 关闭数据库操作对象
*/
public static void closeRs(ResultSet rs){
try{
if(rs!=null){
rs.close();
}
} catch(Exception e){
e.printStackTrace();
}
}
}
4、实现类NewDao（带DBHelper）：操作数据库实现增删改查
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner; public class NewDao { public static void main(String[] args) {
NewDao nd = new NewDao();
nd.show();
// Scanner input = new Scanner(System.in);
//改数据
// System.out.println("请输入要修改的商品编号：");
// int gid = input.nextInt();
// System.out.println("请输入要修改的商品名称：");
// String gname = input.next();
// System.out.println("请输入要修改的商品价格：");
// double gprice = input.nextDouble();
//
// nd.upd(gid, gname, gprice); //查数据
// System.out.println("请输入最低价格：");
// double mprice = input.nextDouble();
// System.out.println("请输入最高价格：");
// double xprice = input.nextDouble();
//
// nd.search(mprice, xprice); }
private Connection conn = null;
private PreparedStatement pstmt = null;
private ResultSet rs = null;
//查数据
public void search(double minprice, double maxprice){
try{
conn = DBHelper.getConn();
String sql = "select * from goods where gprice>=? and gprice<=?";
// 预编译的对象
pstmt = conn.prepareStatement(sql); pstmt.setDouble(1, minprice);
pstmt.setDouble(2, maxprice); rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString("gid")+"#"+rs.getString("gname")+"#"+rs.getString("gprice"));
} } catch(Exception e){
e.printStackTrace();
} finally{
DBHelper.closeRs(rs);
DBHelper.closePstmt(pstmt);
DBHelper.closeConn(conn);
}
} //改数据
public void upd(int gid , String gname, double gprice){
try{
conn = DBHelper.getConn();
String sql = "update goods set gname=?, gprice=? where gid=?"; pstmt = conn.prepareStatement(sql);
pstmt.setString(1, gname);
pstmt.setDouble(2, gprice);
pstmt.setInt(3, gid); pstmt.execute(); } catch(Exception e){
e.printStackTrace();
} finally{
DBHelper.closePstmt(pstmt);
DBHelper.closeConn(conn);
}
} //用集合展示数据
public List<Goods> getAllGoods(){
List<Goods> list = new ArrayList<Goods>();
try{
conn = DBHelper.getConn(); String sql = "select * from goods"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){
Goods goods = new Goods(rs.getInt("gid"),rs.getString("gname"),rs.getDouble("gprice"),rs.getString("gdate")); list.add(goods);
} } catch(Exception e){
e.printStackTrace();
} finally{
DBHelper.closeRs(rs);
DBHelper.closePstmt(pstmt);
DBHelper.closeConn(conn);
}
return list;
} public void show(){
List<Goods> list = getAllGoods(); for(int i=0;i<list.size();i++){
System.out.println(list.get(i).getGid()+"#"+list.get(i).getGname()+"#"+list.get(i).getGprice());
}
} }