[JavaWeb教程]第四章-java数据库开发
浏览:2090次 出处信息
内存中的数据在程序重启或者服务器重启时会丢失，所以数据需要保存在硬盘中，关系型数据库是比较常用的数据存储方式，采用二维表(行列)模型存储的方式更加容易理解，关系型数据库有很多oracle, mysql 和 sql server是比较常用的，java web项目中经常使用mysql作为数据库存储。
下面我们一起看下怎么安装运行mysql数据库，
1、http://pan.baidu.com/s/1nudccMt 通过连接下载mysql安装包
2、把改文件解压到安装目录(自定义)，我的安装目录为”D:\Program Files\mysql-5.6.24-win32″
3、运行目录”D:\Program Files\mysql-5.6.24-win32\bin”的mysqld.exe文件。
运行时会先显示一个命令提示符，不用处理，运行成功后会自动关闭。
为了方便的管理数据库，我们按照一个客户端navicat，
1、http://pan.baidu.com/s/1jHz3a6M 通过链接下载navicat安装包。
2、把文件解压到安装目录(自定义)，我这里是”D:\Program Files (x86)\NavicatforMySQL”
3、运行”navicat.exe”
4、因为这个软件是收费的，目录中key.txt有一个授权码，填写一下就可以了。
5、打开navicat,在左侧连接栏，右键->创建链接，填入一下信息，密码为空
链接成功后，mysql数据库,会显示”information_schema”, “mysql”, “performance_schema”3个数据库信息，mysql这个应用程序严格意义上应该被称为关系型数据库管理系统，这三个才是真正存储数据的数据库。这三个数据库支撑了mysql的管理数据，所以尽量不要动这些数据库数据。
我们创建一个数据库保存学生信息数据，在刚才创建名为”localhost”的连接上创建数据库”student_info”,如下图所示
创建完成后，双击student_info名称，进入该数据库，
新建查询，用于使用sql语言操作数据库。常用的sql语句有5种，创建表，插入数据，更新数据，删除数据，查询数据，下面我们使用学生信息表简单介绍一下这几种操作。
创建表:
[pre]
CREATETABLE`student` ( `id` int(11) NOTNULLAUTO_INCREMENT, `create_time` timestampNOTNULL, `update_time` timestampNOTNULL, `version` int(11) NOTNULLDEFAULT'0', `name` varchar(64) DEFAULTNULLCOMMENT '名字', `sex` char(6) DEFAULTNULLCOMMENT '性别', `birthday` dateDEFAULTNULLCOMMENT '生日', `password` varchar(64) DEFAULTNULLCOMMENT '密码', `info` varchar(256) DEFAULTNULLCOMMENT '自我介绍', PRIMARYKEY(`id`) ); CREATETABLE`hobbys` ( `id` int(11) NOTNULLAUTO_INCREMENT, `create_time` timestampNOTNULL, `update_time` timestampNOTNULL, `version` int(11) NOTNULLDEFAULT'0', `hobby` varchar(16) DEFAULTNULLCOMMENT '爱好', `student_id` int(11) NOTNULL, PRIMARYKEY(`id`) );
[/pre]
上面的语句是创建两个表，学生信息表(student)和爱好(hobbys)表，因为爱好是无法确定个数的，所以每个学生信息在student中是一行，每个爱好作为hobbys表中的一行，通过student_id与student.id关联。
知识点：
创建表的sql语句格式:
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
….
)
表名称和列名称是自定义的，常用数据类型有
int(size) 整型 size为支持长度
timestamp 日期时间类型
varchar(size) 可变长度字符串，可以存储小于size的字符串
char(size) 定长字符串，字符串长度小于size时，右边用空格字符补足
date 日常类型
列后面出现的”NOT NULL” 表示这个列不能为NULL，否则插入数据时会报错不能插入
列后面DEFAULT ‘0’ 表示默认为0，插入时如果这个列没有赋值，为0，如果有值则为传入值。
COMMENT ‘爱好’ 列备注为”爱好”，无实际意义
PRIMARY KEY (`id`) 表示id列是主键，主键必须全表唯一，不能重复
AUTO_INCREMENT 列自增长，常用在整型的主键上
表创建好了，我们向表中添加一条学生信息和两条学生爱好信息：
[pre]
insertintostudent(id, create_time, update_time, version, name, sex, birthday, password, info) values(0, now(), now(), 0, '测试学生', 'male', '1992-01-02', '111111', '您好学校'); select* fromstudent; insertintohobbys(id, create_time, update_time, version, hobby, student_id) values( 0, now(), now(), 0, '游泳', 1); insertintohobbys(id, create_time, update_time, version, hobby, student_id) values( 0, now(), now(), 0, '篮球', 1);
[/pre]
上面的语句是新增一条学生信息，并为这个学生信息增加两条爱好信息。因为student.id是自增长的，所以我们需要确认一下新增的学生id是多少，才能为这个学生添加爱好信息，所以我们在插入学生信息后使用查询所有学生信息语句进行查询。
知识点:
1、插入语句的格式:insert into 表明(列名1， 列名2，…) values (‘值1’， ‘值2’,…)；其中列和值总数一致，位置相对应，比如values中的第一个值是前面第一个列对应的值。字符串类型需要用单引号或者双引号包裹。
2、now()，mysql的函数，当前时间
3、select 列名1,列名2，… from 表名 where 列名1 = ‘值1’ and 列名2 = ‘值2’ 其中 select后的如果*代替列名，表示显示全部列，where语句后的列名1=‘值1’是查询条件，表示满足此条件的数据才会被返回，and是并且满足后面的条件。
4、自增加id，输入值无效，会根据前面的数据id自增长，所以这里我们输入0
我们查询一下学生名字为带有测试字段，并且爱好是篮球的学生信息。
[pre]
selectstudent.* fromstudent , hobbys wherestudent.id = hobbys.student_id andstudent.namelike'%测试%'andhobbys.hobby ='篮球';
[/pre]
多表查询，from后面是要查询的表，where语句中“student.id = hobbys.student_id”是两个表之间的关联字段相等，like是sql中的模糊查询，”%值%“表示包含值的数据，如果前面%没有表示以值开头，如果后面没有%表示以值结尾。
我们把刚才插入的学生信息的自我介绍改成:”hello world”，
[pre]
updatestudent setinfo = 'hello world'whereid = 1;
[/pre]
更新语句的格式为 update 表名 set 列名1 = ‘值1’, 列名2=‘值2’, … where 列名3 = ‘值3’ and 列名4=‘值4’，一般wher提交为主键筛选，否则会多行更新，容易出现问题。
因为手误，我们执行了两次同一个用户的插入，所以我们需要删除一条，代码如下：
[pre]
insertintostudent(id, create_time, update_time, version, name, sex, birthday, password, info) values(0, now(), now(), 0, '测试学生', 'male', '1992-01-02', '111111', '您好学校'); select* fromstudent ; deletefromstudent whereid = 2;
[/pre]
插入一条相同的数据，查询所有数据，确定要删除的用户的id，然后执行删除语句。
语句格式:delete from 表名 where 列名1=‘值1’ and 列名2 = ‘值2’ ….
常用删除以主键id为条件，尽量避免多行删除的操作。
我们使用客户端navicat可以很方便的执行sql语句，进行数据操作。java提供了jdbc中间件支持与数据库的通信，下面我们使用jdbc进行数据的增删改查操作。下载 jdbc链接mysql的jar包，链接地址:http://pan.baidu.com/s/1dEwVS2l 并把jar包添加到WEB-INFO/lib下
示例代码如下:
[pre]
publicclassStudentPo {
privateintid;
privateDate createTime;
privateDate updateTime;
privateintversion;
privateString name;
privateString sex;
privatejava.sql.Date birthdate;
privateString password;
privateString info;
}
publicclassConnectionUtil {
publicstaticConnection getConnection() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/student_info?useUnicode=true&characterEncoding=UTF-8";
String username = "root";
String password = "";
Connection conn = null;
try{
Class.forName(driver); //classLoader,加载对应驱动
conn = (Connection) DriverManager.getConnection(url, username, password);
} catch(ClassNotFoundException e) {
e.printStackTrace();
} catch(SQLException e) {
e.printStackTrace();
}
returnconn;
}
}
publicclassStudentInfoDao {
publicintinsert(StudentPo student) {
Connection conn = ConnectionUtil.getConnection();
intinsertRow = 0;
String sql = "insert into student(id, create_time, update_time, version, name, sex, birthday, password, info)"
+ "values(0, now(), now(), 0, ?, ?, ?, ?, ?)";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setDate(3, student.getBirthdate());
pstmt.setString(4, student.getPassword());
pstmt.setString(5, student.getInfo());
insertRow = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
returninsertRow;
}
publicintupdate(StudentPo student) {
Connection conn = ConnectionUtil.getConnection();
String sql = "update student set name =?, sex = ?, birthday = ?, password = ?, info = ?, update_time = now() where id= ?";
PreparedStatement pstmt = null;
intupdateRow = 0;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setDate(3, student.getBirthdate());
pstmt.setString(4, student.getPassword());
pstmt.setString(5, student.getInfo());
pstmt.setInt(6, student.getId());
updateRow = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
returnupdateRow;
}
publicStudentPo selectStudentPoById(intid) {
Connection conn = ConnectionUtil.getConnection();
String sql = "select * from student where id = ?";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
StudentPo studentPo = newStudentPo();
while(rs.next()) {
studentPo.setBirthdate(rs.getDate("birthday"));
studentPo.setId(rs.getInt("id"));
studentPo.setInfo(rs.getString("info"));
studentPo.setName(rs.getString("name"));
studentPo.setPassword(rs.getString("password"));
studentPo.setSex(rs.getString("sex"));
studentPo.setVersion(rs.getInt("version"));
studentPo.setCreateTime(rs.getTimestamp("create_time"));
studentPo.setUpdateTime(rs.getTimestamp("update_time"));
}
returnstudentPo;
} catch(SQLException e) {
e.printStackTrace();
}
returnnull;
}
publicList<StudentPo> selectStudentPoByName(String name) {
Connection conn = ConnectionUtil.getConnection();
String sql = "select * from student where name = ?";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
ResultSet rs = pstmt.executeQuery();
List<StudentPo> studentList = newArrayList<StudentPo>();
while(rs.next()) {
StudentPo studentPo = newStudentPo();
studentPo.setBirthdate(rs.getDate("birthday"));
studentPo.setId(rs.getInt("id"));
studentPo.setInfo(rs.getString("info"));
studentPo.setName(rs.getString("name"));
studentPo.setPassword(rs.getString("password"));
studentPo.setSex(rs.getString("sex"));
studentPo.setVersion(rs.getInt("version"));
studentPo.setCreateTime(rs.getTimestamp("create_time"));
studentPo.setUpdateTime(rs.getTimestamp("update_time"));
studentList.add(studentPo);
}
returnstudentList;
} catch(SQLException e) {
e.printStackTrace();
}
returnnull;
}
publicList<StudentPo> selectAllStudents() {
Connection conn = ConnectionUtil.getConnection();
String sql = "select * from student ";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
List<StudentPo> studentList = newArrayList<StudentPo>();
while(rs.next()) {
StudentPo studentPo = newStudentPo();
studentPo.setBirthdate(rs.getDate("birthday"));
studentPo.setId(rs.getInt("id"));
studentPo.setInfo(rs.getString("info"));
studentPo.setName(rs.getString("name"));
studentPo.setPassword(rs.getString("password"));
studentPo.setSex(rs.getString("sex"));
studentPo.setVersion(rs.getInt("version"));
studentPo.setCreateTime(rs.getTimestamp("create_time"));
studentPo.setUpdateTime(rs.getTimestamp("update_time"));
studentList.add(studentPo);
}
returnstudentList;
} catch(SQLException e) {
e.printStackTrace();
}
returnnull;
}
publicintdelete(intid) {
Connection conn = ConnectionUtil.getConnection();
String sql = "delete from student where id=?";
PreparedStatement pstmt = null;
introwNum = 0;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
rowNum = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
returnrowNum;
}
} [/pre]
知识点:
1、sql是面向表的语言，java是面向对象的语言，所以java通过jdbc访问数据库时，最好有一个映射类，把sql的操作封闭在Dao(database access object数据库访问层)层，而不向其他的java代码中扩散，因此这里创建了Po(persistent object 持久化对象)对象StudentPo，属性与表列一一对应。
2、java与mysql的交互是通过网络连接实现的，所以我们在执行操作之前需要与mysql建立连接，这里把建立连接的过程封装为一个静态方法中ConnectionUtil.getConnection();
Class.forName(driver); //classLoader,加载对应驱动
conn = (Connection) DriverManager.getConnection(url, username, password); //创建与mysql的连接，url为地址，username用户名，password密码。其中url的格式为jdbc:mysql://ip:端口/数据库名称?useUnicode=true&characterEncoding=UTF8，
”useUnicode=true&characterEncoding=UTF-8“设置字符传输编码方式为utf-8为了解决中文乱码问题。
3、数据的交互模式：
创建链接 ； Connection conn = ConnectionUtil.getConnection();
创建执行语句；pstmt = conn.prepareStatement(sql);
设置参数；pstmt.setInt(1, id);每个”?”表示一个参数，从1开始顺序设置。
执行语句；rowNum = pstmt.executeUpdate();更新(增删改)执行，返回影响的行数
pstmt.executeQuery();查询执行，返回结果集ResultSet
遍历结果集，把数据存储到PO中；rs.next()获取下一行，rs.getString(“name”)获取该行对应单元格的值。
关闭执行语句；pstmt.close();
关闭连接；conn.close();
4、可以开发一个main方法测试以上的功能。这里展示一下插入：
StudentInfoDao studentInfoDao = new StudentInfoDao();
StudentPo student = new StudentPo();
student.setBirthdate(new Date(System.currentTimeMillis()));
student.setName(“jdbc测试”);
student.setSex(“female”);
student.setPassword(“111111”);
student.setInfo(“hello 世界”);
int insertRow = studentInfoDao.insert(student);
System.out.println(“insert 返回值” + insertRow);
5、StudentDao中实现了增加，删除，更新，根据id查询，根据名称查询和查询全部学生信息的功能。
我们把页面也数据库结合起来，完整的实现学生信息管理，通过页面实现学生信息的增删改查等功能。
1、添加爱好的dao访问，分别实现插入，根据学生信息id的查询和删除
[pre]
publicclassHobbyPo {
privateintid;
privateDate createTime;
privateDate updateTime;
privateintversion;
privateString hobby;
privateintstudentId;
}
publicclassHobbyDao {
publicintinsert(HobbyPo hobbyPo) {
Connection conn = ConnectionUtil.getConnection();
intinsertRow = 0;
String sql = "insert into hobbys(id, create_time, update_time, version, hobby, student_id)"
+ "values(0, now(), now(), 0, ?, ?)";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, hobbyPo.getHobby());
pstmt.setInt(2, hobbyPo.getStudentId());
insertRow = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
returninsertRow;
}
publicintdeleteByStudentId(intstudentId) {
Connection conn = ConnectionUtil.getConnection();
String sql = "delete from hobbys where student_id=?";
PreparedStatement pstmt = null;
introwNum = 0;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, studentId);
rowNum = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
returnrowNum;
}
publicList<HobbyPo> selectHobbyPoByStudentId(intstudentId) {
Connection conn = ConnectionUtil.getConnection();
String sql = "select * from hobbys where student_id = ?";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, studentId);
ResultSet rs = pstmt.executeQuery();
List<HobbyPo> hobbyList = newArrayList<HobbyPo>();
while(rs.next()) {
HobbyPo hobby = newHobbyPo();
hobby.setId(rs.getInt("id"));
hobby.setHobby(rs.getString("hobby"));
hobby.setStudentId(rs.getInt("student_id"));
hobby.setVersion(rs.getInt("version"));
hobby.setCreateTime(rs.getTimestamp("create_time"));
hobby.setUpdateTime(rs.getTimestamp("update_time"));
hobbyList.add(hobby);
}
returnhobbyList;
} catch(SQLException e) {
e.printStackTrace();
}
returnnull;
}
}[/pre]
2、添加业务层StudentInfoBo对象，分别实现添加学生信息和展示学生信息列表功能
[pre]
publicList<StudentVo> listAll() {
StudentInfoDao studentInfoDao = newStudentInfoDao();
List<StudentPo> allStudents = studentInfoDao.selectAllStudents();
List<StudentVo> studentVoList = newArrayList<StudentVo>();
if(allStudents != null&& allStudents.size() > 0) {
HobbyDao hobbyDao = newHobbyDao();
for(StudentPo studentPo : allStudents) {
List<HobbyPo> hobbyPoList = hobbyDao.selectHobbyPoByStudentId(studentPo.getId());
StudentVo studentVo = newStudentVo(studentPo, hobbyPoList);
studentVoList.add(studentVo);
}
}
returnstudentVoList;
}
publicbooleanadd(StudentVo studentVo) {
StudentInfoDao studentInfoDao = newStudentInfoDao();
List<StudentPo> selectStudentPoByName = studentInfoDao.selectStudentPoByName(studentVo.getName());
if(selectStudentPoByName.size() != 0) {
returnfalse;
}
StudentPo studentPo = newStudentPo(studentVo);
studentInfoDao.insert(studentPo);
List<StudentPo> studentListByName = studentInfoDao.selectStudentPoByName(studentVo.getName());
StudentPo insertStudentPo = studentListByName.get(0);
if(studentVo.getHobbys() != null&& studentVo.getHobbys().length > 0) {
HobbyDao hobbyDao = newHobbyDao();
for(String hobby : studentVo.getHobbys()) {
HobbyPo hobbyPo = newHobbyPo();
hobbyPo.setHobby(hobby);
hobbyPo.setStudentId(insertStudentPo.getId());
hobbyDao.insert(hobbyPo);
}
}
returntrue;
} [/pre]
查询学生信息列表:先查询学生信息列表，然后根据student.id分别查询爱好信息，并组合成StudentVo进行展示
新增学生信息:根据name查询是否存在，如果存在不进行插入，如果不存在，先插入学生信息，并根据Name查询插入的学生信息获取student.id，然后在分别插入所有的学生爱好信息。
封装了StudentVo以StudentPo和List<HobbyPo>为入参的构造方法，同时也封装了以StudentPo的以StudentVo为入参的构造方法。
3、修改servlet层，把原来操作ServletContext保存信息的代码修改为使用StudentInfoBo的业务方法实现查询学生信息列表和新增学生信息的功能。
小练习：
实现学生信息的更新和删除。
练习中的代码：
[pre]
packagecom.sunhaojie.learntestweb.utils;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.SQLException;
/**
* @ClassName ConnectionUtil
* @Description 链接工具类
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午4:31:02
*/
publicclassConnectionUtil {
/**
*
* @Title createConnection
* @Description 创建jdbc链接
* @return Connection
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午4:37:31
*/
publicstaticConnection getConnection() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/student_info?useUnicode=true&characterEncoding=UTF-8";
String username = "root";
String password = "";
Connection conn = null;
try{
Class.forName(driver); //classLoader,加载对应驱动
conn = (Connection) DriverManager.getConnection(url, username, password);
} catch(ClassNotFoundException e) {
e.printStackTrace();
} catch(SQLException e) {
e.printStackTrace();
}
returnconn;
}
}
packagecom.sunhaojie.learntestweb.po;
importjava.util.Date;
/**
* @ClassName HobbyPo
* @Description 学生爱好Po
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午9:54:26
*/
publicclassHobbyPo {
privateintid;
privateDate createTime;
privateDate updateTime;
privateintversion;
/**
* 爱好
*/
privateString hobby;
/**
* 学生信息id
*/
privateintstudentId;
publicintgetId() {
returnid;
}
publicvoidsetId(intid) {
this.id = id;
}
publicDate getCreateTime() {
returncreateTime;
}
publicvoidsetCreateTime(Date createTime) {
this.createTime = createTime;
}
publicDate getUpdateTime() {
returnupdateTime;
}
publicvoidsetUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
publicintgetVersion() {
returnversion;
}
publicvoidsetVersion(intversion) {
this.version = version;
}
publicString getHobby() {
returnhobby;
}
publicvoidsetHobby(String hobby) {
this.hobby = hobby;
}
publicintgetStudentId() {
returnstudentId;
}
publicvoidsetStudentId(intstudentId) {
this.studentId = studentId;
}
}
packagecom.sunhaojie.learntestweb.po;
importjava.text.DateFormat;
importjava.text.ParseException;
importjava.text.SimpleDateFormat;
importjava.util.Date;
importcom.sunhaojie.learntestweb.vo.StudentVo;
/**
* @ClassName StudentPo
* @Description TODO
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午4:44:21
*/
publicclassStudentPo {
privateintid;
privateDate createTime;
privateDate updateTime;
privateintversion;
/**
* 名字
*/
privateString name;
/**
* 性别
*/
privateString sex;
/**
* 生日
*/
privatejava.sql.Date birthdate;
/**
* 密码
*/
privateString password;
/**
* 自我介绍
*/
privateString info;
publicStudentPo() {
}
publicStudentPo(StudentVo studentVo) {
if(studentVo.getYear() != null&& studentVo.getMonth() != null&& studentVo.getDay() != null) {
DateFormat dateFormat = newSimpleDateFormat("yyyy-MM-dd");
try{
Date birthday = dateFormat.parse(studentVo.getYear() + "-"+ studentVo.getMonth() + "-"
+ studentVo.getDay());
this.birthdate = newjava.sql.Date(birthday.getTime());
} catch(ParseException e) {
e.printStackTrace();
}
}
this.info = studentVo.getInfo();
this.name = studentVo.getName();
this.password = studentVo.getPassword();
this.sex = studentVo.getSex();
}
publicintgetId() {
returnid;
}
publicvoidsetId(intid) {
this.id = id;
}
publicDate getCreateTime() {
returncreateTime;
}
publicvoidsetCreateTime(Date createTime) {
this.createTime = createTime;
}
publicDate getUpdateTime() {
returnupdateTime;
}
publicvoidsetUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
publicintgetVersion() {
returnversion;
}
publicvoidsetVersion(intversion) {
this.version = version;
}
publicString getName() {
returnname;
}
publicvoidsetName(String name) {
this.name = name;
}
publicString getSex() {
returnsex;
}
publicvoidsetSex(String sex) {
this.sex = sex;
}
publicjava.sql.Date getBirthdate() {
returnbirthdate;
}
publicvoidsetBirthdate(java.sql.Date birthdate) {
this.birthdate = birthdate;
}
publicString getPassword() {
returnpassword;
}
publicvoidsetPassword(String password) {
this.password = password;
}
publicString getInfo() {
returninfo;
}
publicvoidsetInfo(String info) {
this.info = info;
}
}
packagecom.sunhaojie.learntestweb.dao;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.List;
importcom.sunhaojie.learntestweb.po.HobbyPo;
importcom.sunhaojie.learntestweb.utils.ConnectionUtil;
/**
* @ClassName HobbyDao
* @Description 学生爱好信息dao
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午9:56:39
*/
publicclassHobbyDao {
/**
*
* @Title insert
* @Description 插入爱好信息
* @param hobbyPo
* @return int
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午10:06:44
*/
publicintinsert(HobbyPo hobbyPo) {
Connection conn = ConnectionUtil.getConnection();
intinsertRow = 0;
String sql = "insert into hobbys(id, create_time, update_time, version, hobby, student_id)"
+ "values(0, now(), now(), 0, ?, ?)";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, hobbyPo.getHobby());
pstmt.setInt(2, hobbyPo.getStudentId());
insertRow = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
returninsertRow;
}
/**
*
* @Title deleteByStudentId
* @Description 根据studentId删除学生爱好信息
* @param studentId
* @return int
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午10:03:51
*/
publicintdeleteByStudentId(intstudentId) {
Connection conn = ConnectionUtil.getConnection();
String sql = "delete from hobbys where student_id=?";
PreparedStatement pstmt = null;
introwNum = 0;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, studentId);
rowNum = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
returnrowNum;
}
/**
*
* @Title selectHobbyPoByStudentId
* @Description 根据studentId查询学生爱好信息
* @param studentId
* @return List<HobbyPo>
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午10:02:28
*/
publicList<HobbyPo> selectHobbyPoByStudentId(intstudentId) {
Connection conn = ConnectionUtil.getConnection();
String sql = "select * from hobbys where student_id = ?";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, studentId);
ResultSet rs = pstmt.executeQuery();
List<HobbyPo> hobbyList = newArrayList<HobbyPo>();
while(rs.next()) {
HobbyPo hobby = newHobbyPo();
hobby.setId(rs.getInt("id"));
hobby.setHobby(rs.getString("hobby"));
hobby.setStudentId(rs.getInt("student_id"));
hobby.setVersion(rs.getInt("version"));
hobby.setCreateTime(rs.getTimestamp("create_time"));
hobby.setUpdateTime(rs.getTimestamp("update_time"));
hobbyList.add(hobby);
}
returnhobbyList;
} catch(SQLException e) {
e.printStackTrace();
}
returnnull;
}
}
packagecom.sunhaojie.learntestweb.dao;
importjava.sql.Connection;
importjava.sql.Date;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.List;
importcom.sunhaojie.learntestweb.po.StudentPo;
importcom.sunhaojie.learntestweb.utils.ConnectionUtil;
/**
* @ClassName StudentInfoDao
* @Description 学生信息方案
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午4:21:53
*/
publicclassStudentInfoDao {
/**
*
* @Title insert
* @Description 插入商品信息
* @param student
* @return int
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午5:51:12
*/
publicintinsert(StudentPo student) {
Connection conn = ConnectionUtil.getConnection();
intinsertRow = 0;
String sql = "insert into student(id, create_time, update_time, version, name, sex, birthday, password, info)"
+ "values(0, now(), now(), 0, ?, ?, ?, ?, ?)";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setDate(3, student.getBirthdate());
pstmt.setString(4, student.getPassword());
pstmt.setString(5, student.getInfo());
insertRow = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
returninsertRow;
}
/**
*
* @Title update
* @Description 更新商品新
* @param student
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午5:51:59
*/
publicintupdate(StudentPo student) {
Connection conn = ConnectionUtil.getConnection();
String sql = "update student set name =?, sex = ?, birthday = ?, password = ?, info = ?, update_time = now() where id= ?";
PreparedStatement pstmt = null;
intupdateRow = 0;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setDate(3, student.getBirthdate());
pstmt.setString(4, student.getPassword());
pstmt.setString(5, student.getInfo());
pstmt.setInt(6, student.getId());
updateRow = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
returnupdateRow;
}
/**
*
* @Title selectStudentPoById
* @Description 根据id查询
* @param id
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午5:52:22
*/
publicStudentPo selectStudentPoById(intid) {
Connection conn = ConnectionUtil.getConnection();
String sql = "select * from student where id = ?";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
StudentPo studentPo = newStudentPo();
while(rs.next()) {
studentPo.setBirthdate(rs.getDate("birthday"));
studentPo.setId(rs.getInt("id"));
studentPo.setInfo(rs.getString("info"));
studentPo.setName(rs.getString("name"));
studentPo.setPassword(rs.getString("password"));
studentPo.setSex(rs.getString("sex"));
studentPo.setVersion(rs.getInt("version"));
studentPo.setCreateTime(rs.getTimestamp("create_time"));
studentPo.setUpdateTime(rs.getTimestamp("update_time"));
}
returnstudentPo;
} catch(SQLException e) {
e.printStackTrace();
}
returnnull;
}
/**
*
* @Title selectStudentPoByName
* @Description 根据名称查询学生信息列表
* @param name
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午6:28:12
*/
publicList<StudentPo> selectStudentPoByName(String name) {
Connection conn = ConnectionUtil.getConnection();
String sql = "select * from student where name = ?";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
ResultSet rs = pstmt.executeQuery();
List<StudentPo> studentList = newArrayList<StudentPo>();
while(rs.next()) {
StudentPo studentPo = newStudentPo();
studentPo.setBirthdate(rs.getDate("birthday"));
studentPo.setId(rs.getInt("id"));
studentPo.setInfo(rs.getString("info"));
studentPo.setName(rs.getString("name"));
studentPo.setPassword(rs.getString("password"));
studentPo.setSex(rs.getString("sex"));
studentPo.setVersion(rs.getInt("version"));
studentPo.setCreateTime(rs.getTimestamp("create_time"));
studentPo.setUpdateTime(rs.getTimestamp("update_time"));
studentList.add(studentPo);
}
returnstudentList;
} catch(SQLException e) {
e.printStackTrace();
}
returnnull;
}
/**
*
* @Title selectStudentPoByName
* @Description 查询全部学生信息列表
* @param name
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午6:28:12
*/
publicList<StudentPo> selectAllStudents() {
Connection conn = ConnectionUtil.getConnection();
String sql = "select * from student ";
PreparedStatement pstmt;
try{
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
List<StudentPo> studentList = newArrayList<StudentPo>();
while(rs.next()) {
StudentPo studentPo = newStudentPo();
studentPo.setBirthdate(rs.getDate("birthday"));
studentPo.setId(rs.getInt("id"));
studentPo.setInfo(rs.getString("info"));
studentPo.setName(rs.getString("name"));
studentPo.setPassword(rs.getString("password"));
studentPo.setSex(rs.getString("sex"));
studentPo.setVersion(rs.getInt("version"));
studentPo.setCreateTime(rs.getTimestamp("create_time"));
studentPo.setUpdateTime(rs.getTimestamp("update_time"));
studentList.add(studentPo);
}
returnstudentList;
} catch(SQLException e) {
e.printStackTrace();
}
returnnull;
}
/**
*
* @Title delete
* @Description 根据id删除学生信息
* @param id
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午7:28:56
*/
publicintdelete(intid) {
Connection conn = ConnectionUtil.getConnection();
String sql = "delete from student where id=?";
PreparedStatement pstmt = null;
introwNum = 0;
try{
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
rowNum = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
returnrowNum;
}
publicstaticvoidmain(String[] args) {
StudentInfoDao studentInfoDao = newStudentInfoDao();
// StudentPo student = new StudentPo();
// student.setBirthdate(new Date(System.currentTimeMillis()));
// student.setName("jdbc测试");
// student.setSex("female");
// student.setPassword("111111");
// student.setInfo("hello 世界");
// int insertRow = studentInfoDao.insert(student);
// System.out.println("insert 返回值" + insertRow);
// StudentPo studentPo = studentInfoDao.selectStudentPoById(1);
// System.out.println(studentPo.getName());
//
// studentPo.setName("jdbcUpdateName学生");
// studentInfoDao.update(studentPo);
StudentPo student = newStudentPo();
student.setBirthdate(newDate(System.currentTimeMillis()));
student.setName("jdbc测试删除");
student.setSex("female");
student.setPassword("111111");
student.setInfo("hello 世界");
intinsert = studentInfoDao.insert(student);
System.out.println("insert 返回值"+ insert);
List<StudentPo> studentList = studentInfoDao.selectStudentPoByName("jdbc测试删除");
for(StudentPo studentPo : studentList) {
System.out.println("删除学生信息，name:"+ studentPo.getName());
studentInfoDao.delete(studentPo.getId());
}
}
}
packagecom.sunhaojie.learntestweb.bo;
importjava.util.ArrayList;
importjava.util.List;
importcom.sunhaojie.learntestweb.dao.HobbyDao;
importcom.sunhaojie.learntestweb.dao.StudentInfoDao;
importcom.sunhaojie.learntestweb.po.HobbyPo;
importcom.sunhaojie.learntestweb.po.StudentPo;
importcom.sunhaojie.learntestweb.vo.StudentVo;
/**
* @ClassName StudentBo
* @Description 学生信息业务类
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午9:45:03
*/
publicclassStudentInfoBo {
/**
*
* @Title listAll
* @Description 获取所有学生信息
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午10:12:05
*/
publicList<StudentVo> listAll() {
StudentInfoDao studentInfoDao = newStudentInfoDao();
List<StudentPo> allStudents = studentInfoDao.selectAllStudents();
List<StudentVo> studentVoList = newArrayList<StudentVo>();
if(allStudents != null&& allStudents.size() > 0) {
HobbyDao hobbyDao = newHobbyDao();
for(StudentPo studentPo : allStudents) {
List<HobbyPo> hobbyPoList = hobbyDao.selectHobbyPoByStudentId(studentPo.getId());
StudentVo studentVo = newStudentVo(studentPo, hobbyPoList);
studentVoList.add(studentVo);
}
}
returnstudentVoList;
}
/**
*
* @Title add
* @Description 添加学生信息，如果存在studentVo.name则返回false
* @param studentVo
* @return boolean
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午10:17:37
*/
publicbooleanadd(StudentVo studentVo) {
StudentInfoDao studentInfoDao = newStudentInfoDao();
List<StudentPo> selectStudentPoByName = studentInfoDao.selectStudentPoByName(studentVo.getName());
if(selectStudentPoByName.size() != 0) {
returnfalse;
}
StudentPo studentPo = newStudentPo(studentVo);
studentInfoDao.insert(studentPo);
List<StudentPo> studentListByName = studentInfoDao.selectStudentPoByName(studentVo.getName());
StudentPo insertStudentPo = studentListByName.get(0);
if(studentVo.getHobbys() != null&& studentVo.getHobbys().length > 0) {
HobbyDao hobbyDao = newHobbyDao();
for(String hobby : studentVo.getHobbys()) {
HobbyPo hobbyPo = newHobbyPo();
hobbyPo.setHobby(hobby);
hobbyPo.setStudentId(insertStudentPo.getId());
hobbyDao.insert(hobbyPo);
}
}
returntrue;
}
}
packagecom.sunhaojie.learntestweb.vo;
importjava.util.Calendar;
importjava.util.List;
importcom.sunhaojie.learntestweb.po.HobbyPo;
importcom.sunhaojie.learntestweb.po.StudentPo;
/**
* @ClassName StudentVo
* @Description 学生信息
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月23日 下午2:07:48
*/
publicclassStudentVo {
/**
* 名字
*/
privateString name;
/**
* 性别
*/
privateString sex;
/**
* 出生年
*/
privateString year;
/**
* 出生月
*/
privateString month;
/**
* 出生日
*/
privateString day;
/**
* 兴趣爱好
*/
privateString[] hobbys;
/**
* 密码
*/
privateString password;
/**
* 自我介绍
*/
privateString info;
publicStudentVo() {
}
/**
* @param studentPo
* @param hobbyPoList
*/
publicStudentVo(StudentPo studentPo, List<HobbyPo> hobbyPoList) {
this.name = studentPo.getName();
this.info = studentPo.getInfo();
this.password = studentPo.getPassword();
this.sex = studentPo.getSex();
if(studentPo.getBirthdate() != null) {
Calendar calendar = Calendar.getInstance();
calendar.setTimeInMillis(studentPo.getBirthdate().getTime());
this.setYear(calendar.get(Calendar.YEAR) + "");
this.setMonth(calendar.get(Calendar.MONTH) + "");
this.setDay(calendar.get(Calendar.DAY_OF_MONTH) + "");
}
if(hobbyPoList != null&& hobbyPoList.size() > 0) {
String[] hobbys = newString[hobbyPoList.size()];
for(inti = 0; i < hobbyPoList.size(); i++) {
hobbys[i] = hobbyPoList.get(i).getHobby();
}
this.hobbys = hobbys;
}
}
publicString getName() {
returnname;
}
publicvoidsetName(String name) {
this.name = name;
}
publicString getSex() {
returnsex;
}
publicvoidsetSex(String sex) {
this.sex = sex;
}
publicString getYear() {
returnyear;
}
publicvoidsetYear(String year) {
this.year = year;
}
publicString getMonth() {
returnmonth;
}
publicvoidsetMonth(String month) {
this.month = month;
}
publicString getDay() {
returnday;
}
publicvoidsetDay(String day) {
this.day = day;
}
publicString[] getHobbys() {
returnhobbys;
}
publicvoidsetHobbys(String[] hobbys) {
this.hobbys = hobbys;
}
publicString getPassword() {
returnpassword;
}
publicvoidsetPassword(String password) {
this.password = password;
}
publicString getInfo() {
returninfo;
}
publicvoidsetInfo(String info) {
this.info = info;
}
}
packagecom.sunhaojie.learntestweb.web;
importjava.util.List;
importjavax.servlet.http.HttpServletRequest;
importjavax.servlet.http.HttpServletResponse;
importorg.apache.velocity.Template;
importorg.apache.velocity.context.Context;
importorg.apache.velocity.tools.view.VelocityViewServlet;
importcom.sunhaojie.learntestweb.bo.StudentInfoBo;
importcom.sunhaojie.learntestweb.vo.StudentVo;
/**
* @ClassName StudentListServlet
* @Description 学生信息列表
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月23日 下午4:33:28
*/
publicclassStudentListServlet extendsVelocityViewServlet {
@Override
protectedTemplate handleRequest(HttpServletRequest request, HttpServletResponse response, Context ctx) {
StudentInfoBo studentInfoBo = newStudentInfoBo();
List<StudentVo> studentList = studentInfoBo.listAll();
ctx.put("studentList", studentList);
returngetTemplate("studentList.vm");
}
}
packagecom.sunhaojie.learntestweb.web;
importjava.io.IOException;
importjavax.servlet.ServletException;
importjavax.servlet.http.HttpServlet;
importjavax.servlet.http.HttpServletRequest;
importjavax.servlet.http.HttpServletResponse;
importcom.sunhaojie.learntestweb.bo.StudentInfoBo;
importcom.sunhaojie.learntestweb.vo.StudentVo;
/**
* @ClassName SubmitStudentInfoServlet
* @Description 学生信息提交
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月23日 下午2:02:08
*/
publicclassSubmitStudentInfoServlet extendsHttpServlet {
@Override
protectedvoiddoGet(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {
doPost(req, resp);
}
@SuppressWarnings("unchecked")
@Override
protectedvoiddoPost(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {
req.setCharacterEncoding("UTF-8");
String name = req.getParameter("name");
String sex = req.getParameter("sex");
String year = req.getParameter("year");
String month = req.getParameter("month");
String day = req.getParameter("day");
String[] hobbys = req.getParameterValues("hobby");
String password = req.getParameter("password");
String info = req.getParameter("info");
StudentVo studentVo = newStudentVo();
studentVo.setName(name);
studentVo.setSex(sex);
studentVo.setYear(year);
studentVo.setMonth(month);
studentVo.setDay(day);
studentVo.setHobbys(hobbys);
studentVo.setPassword(password);
studentVo.setInfo(info);
StudentInfoBo studentInfoBo = newStudentInfoBo();
booleanflag = studentInfoBo.add(studentVo);
resp.setContentType("text/html; charset=UTF-8");
if(flag == false) {
resp.getWriter().print(("学生名称已存在:"+ studentVo.getName()));
return;
}
resp.sendRedirect("studentList");
}
}建议继续学习：
QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习
扫一扫订阅我的微信号：IT技术博客大学习
<< 前一篇：[JavaWeb教程]第三章-Servlet开发
后一篇：【Java并发编程实战】—– AQS(一)：简介 >>
文章信息
- 作者：sunhaojie 来源： 孙豪杰的博客
- 标签： JavaWeb教程
- 发布时间：2016-03-07 23:42:18
建议继续学习
近3天十大热文
-
[72] Twitter/微博客的学习摘要 -
[65] find命令的一点注意事项
-
[63] IOS安全–浅谈关于IOS加固的几种方法
-
[63] Go Reflect 性能
-
[62] 如何拿下简短的域名
-
[61] Oracle MTS模式下 进程地址与会话信
-
[61] android 开发入门
-
[60] 流程管理与用户研究
-
[58] 【社会化设计】自我（self）部分――欢迎区
-
[57] 读书笔记-壹百度:百度十年千倍的29条法则
