JDBC基于MVC架构项目实例.docx
《JDBC基于MVC架构项目实例.docx》由会员分享,可在线阅读,更多相关《JDBC基于MVC架构项目实例.docx(47页珍藏版)》请在冰豆网上搜索。
JDBC基于MVC架构项目实例
JDBC基于MVC架构项目实例-实现对MySQL数据表的增删改查、调用数据表中的存储过程和函数
一需求分析
目的:
实现Java程序对MySQL数据库的增删改查操作,……。
工具:
MySQL数据库,Eclipse,NavicatforMySQL数据库管理工具
设计模式:
MVC三层架构
这里写图片描述
模型层对应数据库的映射,对数据库映射的抽象方法(增删改查)
控制层控制数据的流通,把数据拼装给视图层
视图层数据的显示
从下往上依次开发
这里写图片描述
代码分离,分工协作
二编写JavaProject
新建项目jdbc,在新建目录lib下存放驱动mysql-connector-java-5.1.39-bin.jar,添加为BuildPath。
JDBC-MySQL驱动下载
JDK版本:
1.8.0_91
项目目录:
这里写图片描述
三建立数据库层(db层)
建立数据库的语句:
数据库名:
peng
表名:
imooc_goddess
/*
NavicatMySQLDataTransfer
SourceServer:
localhost_3306
SourceServerVersion:
50712
SourceHost:
localhost:
3306
SourceDatabase:
peng
TargetServerType:
MYSQL
TargetServerVersion:
50712
FileEncoding:
65001
Date:
2016-10-0420:
53:
44
*/
SETFOREIGN_KEY_CHECKS=0;
------------------------------
--Tablestructureforimooc_goddess
------------------------------
DROPTABLEIFEXISTS`imooc_goddess`;
CREATETABLE`imooc_goddess`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`user_name`varchar(30)NOTNULL,
`sex`int(11)DEFAULTNULL,
`age`int(11)DEFAULTNULL,
`birthday`dateDEFAULTNULL,
`email`varchar(30)DEFAULTNULL,
`mobile`varchar(11)DEFAULTNULL,
`create_user`varchar(30)DEFAULTNULL,
`create_date`dateDEFAULTNULL,
`update_user`varchar(30)DEFAULTNULL,
`update_date`dateDEFAULTNULL,
`isdel`int(11)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=15DEFAULTCHARSET=utf8;
------------------------------
--Recordsofimooc_goddess
------------------------------
INSERTINTO`imooc_goddess`VALUES('2','小彭',null,'23',null,null,null,null,null,null,null,null);
INSERTINTO`imooc_goddess`VALUES('6','胖子','0','78','2016-10-03','hongming@','1211555599','admin','2016-10-03','admin','2016-10-03','1');
INSERTINTO`imooc_goddess`VALUES('7','小溪','0','34','2016-10-03','hongming@','1211555599','admin','2016-10-03','admin','2016-10-03','1');
INSERTINTO`imooc_goddess`VALUES('9','小霞',null,'23','1990-09-09','xiaoxia@','232445455',null,'2016-10-03',null,'2016-10-03',null);
INSERTINTO`imooc_goddess`VALUES('10','hh','1','23','1990-09-09','jkjfskf','12323','Admin','2016-10-03','admin','2016-10-03','1');
INSERTINTO`imooc_goddess`VALUES('11','平','1','23','2998-04-09','jjjj@','1323','admin','2016-10-03','admin','2016-10-03','1');
INSERTINTO`imooc_goddess`VALUES('12','航母','1','23','2333-09-09','jkksjkjf','1232','admin','2016-10-03','admin','2016-10-03','1');
INSERTINTO`imooc_goddess`VALUES('13','胖纸','1','23','1991-09-09','jjijijij','1323244','admin','2016-10-03','admin','2016-10-03','1');
INSERTINTO`imooc_goddess`VALUES('14','校长','1','18','1998-09-09','jkjijij@','112323424','admin','2016-10-03','admin','2016-10-03','1');
测试数据库连接
所属包:
packagecom.jxust.test
TestDBUtil.java
//packagecom.jxust.test;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
publicclassTestDBUtil{
privatestaticfinalStringURL="jdbc:
mysql:
//127.0.0.1:
3306/peng?
characterEncoding=utf8&useSSL=false";
privatestaticfinalStringUSER="root";
privatestaticfinalStringPASSWORD="root";
privatestaticConnectionconn=null;
publicstaticvoidmain(String[]args)throwsException{
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库连接
conn=DriverManager.getConnection(URL,USER,PASSWORD);
//3.通过数据库的连接操作数据库,实现增删改查
/**
*Statement方法普通的不带参的查询SQL每次执行sql语句,数据库都要执行sql语句的编译
*最好用于仅执行一次查询并返回结果的情形Statementstmt=conn.createStatement();
*ResultSetrs=stmt.executeQuery("selectuser_name,agefrom
*imooc_goddess");
*/
/**
*PreparedStatement方法可变参数的SQL,编译一次,执行多次,效率高
*/
//取user_name和age数据
PreparedStatementptmt=conn.prepareStatement("selectuser_name,agefromimooc_goddess");
ResultSetrs=ptmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString("user_name")+","+rs.getString("age"));
}
}
执行结果:
胖子,78
小溪,34
hh,24
平,32
航母,24
胖纸,26
小那,null
晓华,null
我的数据库连接类-DBUtil
调用getConn方法就可以的到数据库连接对象conn。
所属包:
packagecom.jxust.db
DBUtil.java
//packagecom.jxust.db;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.SQLException;
publicclassDBUtil{
privatestaticfinalStringURL="jdbc:
mysql:
//127.0.0.1:
3306/peng?
characterEncoding=utf8&useSSL=false";
privatestaticfinalStringUSER="root";
privatestaticfinalStringPASSWORD="root";
privatestaticConnectionconn=null;
static{//加载类时会执行这些静态的代码块
try{
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库连接
conn=DriverManager.getConnection(URL,USER,PASSWORD);
}catch(ClassNotFoundExceptione){
e.printStackTrace();
}catch(SQLExceptione){
e.printStackTrace();
}
}
publicstaticConnectiongetConn(){
returnconn;
}
}
当前项目结构:
这里写图片描述
四建立模型层(model层)
创建数据库表对应实体类Goddess。
所属包:
packagecom.jxust.model
Goddess.java
//packagecom.jxust.model;
importjava.util.Date;
publicclassGoddess{
privateIntegerid;
privateStringuser_name;
privateIntegersex;
privateIntegerage;
privateDatebirthday;
privateStringemail;
privateStringmobile;
privateStringcreate_user;
privateStringupdate_user;
privateDatecreate_date;
privateDateupdate_date;
privateIntegerisdel;
publicIntegergetId(){
returnid;
}
publicvoidsetId(Integerid){
this.id=id;
}
publicStringgetUser_name(){
returnuser_name;
}
publicvoidsetUser_name(Stringuser_name){
this.user_name=user_name;
}
publicIntegergetSex(){
returnsex;
}
publicvoidsetSex(Integersex){
this.sex=sex;
}
publicIntegergetAge(){
returnage;
}
publicvoidsetAge(Integerage){
this.age=age;
}
publicDategetBirthday(){
returnbirthday;
}
publicvoidsetBirthday(Datebirthday){
this.birthday=birthday;
}
publicStringgetEmail(){
returnemail;
}
publicvoidsetEmail(Stringemail){
this.email=email;
}
publicStringgetMobile(){
returnmobile;
}
publicvoidsetMobile(Stringmobile){
this.mobile=mobile;
}
publicStringgetCreate_user(){
returncreate_user;
}
publicvoidsetCreate_user(Stringcreate_user){
this.create_user=create_user;
}
publicStringgetUpdate_user(){
returnupdate_user;
}
publicvoidsetUpdate_user(Stringupdate_user){
this.update_user=update_user;
}
publicDategetCreate_date(){
returncreate_date;
}
publicvoidsetCreate_date(Datecreate_date){
this.create_date=create_date;
}
publicDategetUpdate_date(){
returnupdate_date;
}
publicvoidsetUpdate_date(Dateupdate_date){
this.update_date=update_date;
}
publicIntegergetIsdel(){
returnisdel;
}
publicvoidsetIsdel(Integerisdel){
this.isdel=isdel;
}
@Override
publicStringtoString(){
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+"]";
}
}
五建立数据库访问层(dao层)
对应数据库增删改查的方法,用来访问数据库实现数据的持久化。
所属包:
packagecom.jxust.dao
GoddessDao.java
//packagecom.jxust.dao;
importjava.sql.Connection;
importjava.sql.Date;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.text.ParseException;
importjava.text.SimpleDateFormat;
importjava.util.ArrayList;
importjava.util.List;
importjava.util.Map;
//importcom.jxust.db.DBUtil;
//importcom.jxust.model.Goddess;
/**
*类中含有4个查询方法,增删改方法各一个
*@authorPeng
*
*/
publicclassGoddessDao{
/**
*增
*向数据库添加数据
*
*@paramg
*@throwsSQLException
*/
publicvoidaddGoddess(Goddessg)throwsSQLException{
Connectionconn=DBUtil.getConn();
Stringsql=""+"insertintoimooc_goddess"+"(user_name,sex,age,birthday,email,mobile,"
+"create_user,create_date,update_user,update_date,isdel)"
+"values(?
1,?
?
?
?
'admin',current_date(),'admin',current_date(),1)";
PreparedStatementptmt=conn.prepareStatement(sql);
ptmt.setString(1,g.getUser_name());
//ptmt.setInt(2,g.getSex());
ptmt.setInt(2,g.getAge());
ptmt.setDate(3,newDate(g.getBirthday().getTime()));
ptmt.setString(4,g.getEmail());
ptmt.setString(5,g.getMobile());
//ptmt.setString(7,g.getCreate_user());
//ptmt.setString(8,g.getUpdate_user());
//ptmt.setInt(9,g.getIsdel());
ptmt.execute();
}
/**
*改
*通过拼接SQL更新语句,更新数据库中的数据
*
*@paramg
*@throwsSQLException
*@throwsParseException
*/
publicvoidupdateGoddess(Goddessg)throwsSQLException,ParseException{
Connectionconn=DBUtil.getConn();
StringBuildersb=newStringBuilder();
System.out.println(g.toString());
sb.append("updateimooc_goddesssetsex=1");
if(!
g.getUser_name().equals("null")){
sb.append(",user_name="+"'"+g.getUser_name()+"'");
}
if(!
g.getAge().equals(0)){
sb.append(",age="+g.getAge());
}
SimpleDateFormatsf=newSimpleDateFormat("yy-MM-dd");
StringDateStr1="1970-01-01";
java.util.Datedate=null;
date=(java.util.Date)sf.parse(DateStr1);
if(!
((newDate(g.getBirthday().getTime()).compareTo(date))==0)){
sb.append(",birthday="+"'"+(newDate(g.getBirthday().getTime())+"'"));
}
if(!
g.getEmail().equals("null")){
sb.append(",email="+"'"+g.getEmail()+"'");
}
if(!
g.getMobile().equals("null")){
sb.append(",mobile="+"'"+g.getMobile()+"'");
}
if(!
g.getId().equals("null")){
sb.append("whereid="+g.getId());
}
//System.out.println(sb.toString());
PreparedStatementptmt=conn.prep