这个是关于JDBC的小例子
主要是数据库的增删改查
分为MVC三层,通过VIEW层操作
但是不知道为什么只有add和delete的方法能完整显示出来,更新和查询只显示if(step==1)这一步程序就不能执行显示了,但是步骤都和add()方法的基本一样,不知道为什么不行/(ㄒoㄒ)/~~
是 step 定义的问题吗?? 想了很久也不能解决这个问题/(ㄒoㄒ)/~~
求各路英雄帮帮忙/(ㄒoㄒ)/~~
一下是源代码
项目结构----------------------------------------------------------------------
增加功能显示结果-------------------------------------------------------------------
更新功能显示结果-----------------------------------------------------------
查询功能显示结果---------------------------------------------------------------
以下是源代码(5个类)
View.java-----------------------------------------------
package view;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import action.GoddessAction;
import model.Goddess;
public class View {
private static final String CONTEXT="欢迎来到女神禁区:\n" + "下面是女神禁区的功能列表:\n" + "[MAIN/M]:主菜单\n" + "[QUERY/Q]:查看全部女神的信息\n" + "[GET/G]:查看某位女神的详细信息\n" + "[ADD/A]:添加女神信息\n" + "[UPDATE/U]:更新女神信息\n" + "[DELETE/D]:删除女神信息\n" + "[SEARCH/S]:查询女神信息(根据姓名、手机号来查询)\n" + "[EXIT/E]:退出女神禁区\n" + "[BREAK/B]:退出当前功能,返回主菜单"; private static final String OPERATION_MAIN="MAIN"; private static final String OPERATION_QUERY="QUERY"; private static final String OPERATION_GET="GET"; private static final String OPERATION_ADD="ADD"; private static final String OPERATION_UPDATE="UPDATE"; private static final String OPERATION_DELETE="DELETE"; private static final String OPERATION_SEARCH="SEARCH"; private static final String OPERATION_EXIT="EXIT"; private static final String OPERATION_BREAK="BREAK"; public static void main(String[] args) { System.out.println(CONTEXT); Scanner scan = new Scanner(System.in); Goddess goddess = new Goddess(); Integer step = 1; String prenious=null; GoddessAction goddessAction = new GoddessAction(); while(scan.hasNext()){ String in = scan.next(); if(OPERATION_ADD.equals(in.toUpperCase()) ||OPERATION_ADD.substring(0, 1).equals(in.toUpperCase()) ||OPERATION_ADD.equals(prenious)){ prenious = OPERATION_ADD; if(step == 1){ System.out.println("请输入你所要添加的女神[姓名]:"); }else if(step == 2){ goddess.setUser_name(in); System.out.println("请输入你所要添加的女神[性别]:"); }else if(step == 3){ goddess.setSex(Integer.valueOf(in)); System.out.println("请输入你所要添加的女神[年龄]:"); }else if(step == 4){ goddess.setAge(Integer.valueOf(in)); System.out.println("请输入你所要添加的女神[生日]:"); }else if(step == 5){ SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd"); Date birthday=null; try { birthday = sf.parse(in); goddess.setBirthday(birthday); } catch (ParseException e) { // TODO Auto-generated catch block e.printStackTrace(); System.out.println("您输入的格式有错误,请重新输入"); step=5; } System.out.println("请输入你所要添加的女神[邮箱]:"); }else if(step == 6){ goddess.setEmail(in); System.out.println("请输入你所要添加的女神[手机]:"); }else if(step == 7){ goddess.setMobile(in); try { goddessAction.add(goddess); System.out.println("添加女神成功"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); System.out.println("新增女神失败"); } } if(OPERATION_ADD.equals(prenious)){ step++; } }else if(OPERATION_DELETE.equals(in.toUpperCase()) || OPERATION_DELETE.substring(0, 1).equals(in.toUpperCase())){ try { System.out.println("请输入您想要删除的女神"); String deleteIn = scan.next(); goddessAction.delete(Integer.valueOf(deleteIn)); System.out.println("成功删除所选女神!"); } catch (NumberFormatException e) { // TODO Auto-generated catch block System.out.println("删除女神失败!"); e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }else if(OPERATION_UPDATE.equals(in.toUpperCase()) || OPERATION_UPDATE.substring(0, 1).equals(in.toUpperCase())){ prenious = OPERATION_UPDATE; if(step == 1){ System.out.println("请输入你所要更新的用户名"); }else if(step == 2){ goddess.setUser_name(in); System.out.println("请输入你所要更新的年龄"); }else if(step == 3){ goddess.setAge(Integer.valueOf(in)); System.out.println("请输入你所要更新的生日"); }else if(step == 4){ SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd"); Date birthday = null; try { birthday = sf.parse(in); goddess.setBirthday(birthday); System.out.println("请输入你所要更新的邮件"); } catch (ParseException e) { // TODO Auto-generated catch block System.out.println("您输入的日期格式错误!"); e.printStackTrace(); } }else if(step == 5){ goddess.setEmail(in); System.out.println("请输入你所要更新的手机号"); }else if(step == 6){ goddess.setMobile(in); System.out.println("请输入你所要更新的ID"); }else if(step == 7){ try { goddess.setId(Integer.valueOf(in)); goddessAction.update(goddess); System.out.println("更新女神成功!"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(OPERATION_UPDATE.equals(prenious)){ step++; } } else if(OPERATION_QUERY.equals(in.toUpperCase()) || OPERATION_QUERY.substring(0, 1).equals(in.toUpperCase())){ List
GoddessAction.java-----------------------------------------------
package action;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import model.Goddess;
import dao.GodessDao;
package action;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import model.Goddess;
import dao.GodessDao;
public class GoddessAction {
GodessDao godessDao = new GodessDao(); public void add(Goddess goddess) throws SQLException{ godessDao.add(goddess); } public void delete(Integer id) throws SQLException{ godessDao.delete(id); } public void update(Goddess goddess) throws SQLException{ godessDao.update(goddess); } public Listquery() throws Exception{ return godessDao.query(); } public List queryFlex(List > params) throws Exception{ return godessDao.queryFlex(params); }
}
GoddessDao.java-----------------------------------------------
package dao;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import db.DBUtil;
import model.Goddess;
/*
*数据库的增删改查
*/
public class GodessDao {
/* * 增加 */ public void add(Goddess goddess) throws SQLException{ //获得数据库连接 Connection conn = DBUtil.getConnection(); //编写sql语句 String sql = "" + "insert into imooc_goddess" + "(user_name,sex,age,birthday,email,mobile," + "create_user,create_date,update_user,update_date,isdel)" + "values("+ "?,?,?,?,?,?,'admin',current_date(),'admin',current_date(),1)"; //预编译 PreparedStatement preparedStatement = conn.prepareStatement(sql); //传参赋值 preparedStatement.setString(1,goddess.getUser_name()); preparedStatement.setInt(2, goddess.getSex()); preparedStatement.setInt(3,goddess.getAge() ); preparedStatement.setDate(4, new Date(goddess.getBirthday().getTime())); preparedStatement.setString(5,goddess.getEmail() ); preparedStatement.setString(6, goddess.getMobile()); /*preparedStatement.setString(7,goddess.getCreate_user() ); preparedStatement.setString(8,goddess.getUpdate_user()); preparedStatement.setInt(9, goddess.getIsdel());*/ //执行sql语句 preparedStatement.execute(); } /* * 删除 */ public void delete(Integer id) throws SQLException{ Connection conn = DBUtil.getConnection(); String sql = "" + " delete from imooc_goddess"+ " where id=? "; PreparedStatement preparedStatement = conn.prepareStatement(sql); preparedStatement.setInt(1, id); preparedStatement.execute(); } /* * 更新 */ public void update(Goddess goddess) throws SQLException{ Connection conn= DBUtil.getConnection(); //记得sql语句前后加空格,不然报报错误,因为不加空格语句会成一行来执行 String sql = "" + " update imooc_goddess "+ " set user_name=?,sex=1,age=?,birthday=?,email=?,mobile=?, "+ " update_user='admin',update_date=current_date(),isdel=1 "+ " where id=? "; PreparedStatement preparedStatement = conn.prepareStatement(sql); preparedStatement.setString(1, goddess.getUser_name()); /* preparedStatement.setInt(2, goddess.getSex());*/ preparedStatement.setInt(2, goddess.getAge()); preparedStatement.setDate(3, new Date(goddess.getBirthday().getTime())); preparedStatement.setString(4, goddess.getEmail()); preparedStatement.setString(5, goddess.getMobile()); /*preparedStatement.setString(6, goddess.getUpdate_user());*/ /*preparedStatement.setInt(8, goddess.getIsdel())*/; preparedStatement.setInt(6, goddess.getId()); preparedStatement.execute(); } /* * 查询全部 */ public Listquery() throws Exception{ //获得数据库连接 Connection conn = DBUtil.getConnection(); Statement statement = conn.createStatement(); ResultSet resultSet = statement.executeQuery("select user_name,age from imooc_goddess"); List goddessesList = new ArrayList (); Goddess goddess = null; while(resultSet.next()){ goddess=new Goddess(); goddess.setAge(resultSet.getInt("age")); goddess.setUser_name(resultSet.getString("user_name")); goddessesList.add(goddess); } return goddessesList; } /* * 灵活查询 */ public List queryFlex(List > params) throws Exception{ List result = new ArrayList (); //获得数据库连接 Connection conn = DBUtil.getConnection(); StringBuilder sb = new StringBuilder();//单线程适用StringBuilder sb.append(" select * from imooc_goddess where 1=1 ");//注意where1=1(永远为true)的好处,即使and后面的sql的语句,map取到的值为空,也可以执行这一句 if(params!=null && params.size()>0){ for(int i=0;i map = params.get(i); sb.append(" and " + map.get("name")+" " + map.get("rel") +" "+ map.get("value")+" "); } } PreparedStatement preparedStatement = conn.prepareStatement(sb.toString()); System.out.println("SQL语句: "+sb.toString()); ResultSet resultSet = preparedStatement.executeQuery(); Goddess g = null; while(resultSet.next()){ g=new Goddess(); g.setId( resultSet.getInt("id")); g.setUser_name( resultSet.getString("user_name")); g.setAge( resultSet.getInt("age")); g.setSex( resultSet.getInt("sex")); g.setBirthday( resultSet.getDate("birthday")); g.setEmail( resultSet.getString("email")); g.setMobile( resultSet.getString("mobile")); g.setCreate_date( resultSet.getDate("create_date")); g.setCreate_user( resultSet.getString("create_user")); g.setUpdate_date( resultSet.getDate("update_date")); g.setUpdate_user( resultSet.getString("update_user")); g.setIsdel( resultSet.getInt("isdel")); result.add(g); } return result; } /* * 查询单个(根据ID) * 注意:只有增删改操作才使用preparedStatement.execute()方法,查询操作不使用; */ public Goddess queryOne(Integer id) throws SQLException{ Goddess goddess = null; Connection conn = DBUtil.getConnection(); String sql = " "+ " select * from imooc_goddess "+ " where id=? "; PreparedStatement preparedStatement = conn.prepareStatement(sql); preparedStatement.setInt(1, id); ResultSet resultSet = preparedStatement.executeQuery(); while(resultSet.next()){ goddess = new Goddess(); goddess.setId(resultSet.getInt("id")); goddess.setUser_name(resultSet.getString("user_name")); goddess.setAge(resultSet.getInt("age")); goddess.setSex(resultSet.getInt("sex")); //java.sql.date可以自动转换为java.util.date; 因为java.util.Date 是 java.sql.Date 的父类 goddess.setBirthday(resultSet.getDate("birthday")); goddess.setEmail(resultSet.getString("email")); goddess.setMobile(resultSet.getString("mobile")); goddess.setCreate_user(resultSet.getString("create_user")); goddess.setCreate_date(resultSet.getDate("create_date")); goddess.setUpdate_user(resultSet.getString("update_user")); goddess.setUpdate_date(resultSet.getDate("update_date")); goddess.setIsdel(resultSet.getInt("isdel")); } return goddess; }
}
Goddess.java-----------------------------------------------
package model;
import java.util.Date;
public class Goddess {
private Integer id; private String user_name; private Integer sex; private Integer age; private Date birthday; private String email; private String mobile; private String create_user; private String update_user; private Date create_date; private Date update_date; private Integer isdel; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUser_name() { return user_name; } public void setUser_name(String user_name) { this.user_name = user_name; } public Integer getSex() { return sex; } public void setSex(Integer sex) { this.sex = sex; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getMobile() { return mobile; } public void setMobile(String mobile) { this.mobile = mobile; } public String getCreate_user() { return create_user; } public void setCreate_user(String create_user) { this.create_user = create_user; } public String getUpdate_user() { return update_user; } public void setUpdate_user(String update_user) { this.update_user = update_user; } public Date getCreate_date() { return create_date; } public void setCreate_date(Date create_date) { this.create_date = create_date; } public Date getUpdate_date() { return update_date; } public void setUpdate_date(Date update_date) { this.update_date = update_date; } public Integer getIsdel() { return isdel; } public void setIsdel(Integer isdel) { this.isdel = isdel; } @Override public String toString() { return "Goddess [id="+ id +" , user_name=" + user_name + ", sex=" + sex + ", age=" + age + ", birthday=" + birthday + ", email=" + email + ", mobile=" + mobile + ", create_user=" + create_user + ", update_user=" + update_user + ", create_date=" + create_date + ", update_date=" + update_date + ", isdel=" + isdel + "]"; }
}
DBUtil.java-----------------------------------------------
package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
//一般情况下,本机地址都是127.0.0.1 private static final String URL="jdbc:mysql://127.0.0.1:3306/imooc_demo?useUnicode=true&characterEncoding=utf-8"; private static final String USER="root"; private static final String PASSWORD="123456"; private static Connection conn=null; static { try { //1.加载驱动程序 Class.forName("com.mysql.jdbc.Driver"); //2.获得数据库连接 conn=DriverManager.getConnection(URL, USER, PASSWORD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } public static Connection getConnection(){ return conn; }
}
可以试试 让对象编程
hahaha
目前只看了第一个类 View.java,里面的代码逻辑感觉有一点问题,每次使用了 step 变量以后,好像没有恢复初值吧?
有男朋友了么?
我想说女神,你的电话号码泄露了……