这个是关于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<Map<String,Object>> params = new ArrayList<Map<String,Object>>();
Map<String,Object> param = new HashMap<String, Object>();
if(step == 1){
System.out.println("请输入您要查询的信息的[列名]");
}else if(step == 2){
param.put("name", in);
System.out.println("请输入您要查询的信息的[关系]");
}else if(step == 3){
param.put("rel", in);
System.out.println("请输入您要查询的信息的[值]");
}else if(step == 4){
param.put("value", in);
}else if(step == 5){
try {
System.out.println("以下是我们为您所查询到的信息");
params.add(param);
goddessAction.queryFlex(params);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
step++;
}
}
}
}
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 List<Goddess> query() throws Exception{
return godessDao.query();
}
public List<Goddess> queryFlex(List<Map<String, Object>> 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 List<Goddess> query() throws Exception{
//获得数据库连接
Connection conn = DBUtil.getConnection();
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery("select user_name,age from imooc_goddess");
List<Goddess> goddessesList = new ArrayList<Goddess>();
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<Goddess> queryFlex(List<Map<String,Object>> params) throws Exception{
List<Goddess> result = new ArrayList<Goddess>();
//获得数据库连接
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<params.size();i++){
Map<String, Object> 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 变量以后，好像没有恢复初值吧？