关系数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系模型是由埃德加·科德于1970年首先提出的,并配合“科德十二定律”。现如今虽然对此模型有一些批评意见,但它还是数据存储的传统标准。标准数据查询语言SQL就是一种基于关系数据库的语言,这种语言执行对关系数据库中数据的检索和操作。 关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成。
关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。当前主流的关系型数据库有Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL等。
SQL(Structured Query Language)语言是1974年由Boyce和Chamberlin提出的一种介于关系代数与关系演算之间的结构化查询语言,是一个通用的、功能极强的关系型数据库语言。

—————————————————————————————————————————

内存中的数据在程序重启或者服务器重启时会丢失,所以数据需要保存在硬盘中,关系型数据库是比较常用的数据存储方式,采用二维表(行列)模型存储的方式更加容易理解,关系型数据库有很多oracle, mysql 和 sql server是比较常用的,java web项目中经常使用mysql作为数据库存储。

下面我们一起看下怎么安装运行mysql数据库,
1、http://pan.baidu.com/s/1nudccMt 通过连接下载mysql安装包
2、把改文件解压到安装目录(自定义),我的安装目录为”D:Program Filesmysql-5.6.24-win32″
3、运行目录”D:Program Filesmysql-5.6.24-win32bin”的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,在左侧连接栏,右键->创建链接,填入一下信息,密码为空

java_mysql1
链接成功后,mysql数据库,会显示”information_schema”, “mysql”, “performance_schema”3个数据库信息,mysql这个应用程序严格意义上应该被称为关系型数据库管理系统,这三个才是真正存储数据的数据库。这三个数据库支撑了mysql的管理数据,所以尽量不要动这些数据库数据。

我们创建一个数据库保存学生信息数据,在刚才创建名为”localhost”的连接上创建数据库”student_info”,如下图所示

java_mysql2

创建完成后,双击student_info名称,进入该数据库,

java_mysql3

新建查询,用于使用sql语言操作数据库。常用的sql语句有5种,创建表,插入数据,更新数据,删除数据,查询数据,下面我们使用学生信息表简单介绍一下这几种操作。
创建表:

[pre]

[code lang=”sql”]
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` timestamp NOT NULL ,
`update_time` timestamp NOT NULL ,
`version` int(11) NOT NULL DEFAULT ‘0’,
`name` varchar(64) DEFAULT NULL COMMENT ‘名字’,
`sex` char(6) DEFAULT NULL COMMENT ‘性别’,
`birthday` date DEFAULT NULL COMMENT ‘生日’,
`password` varchar(64) DEFAULT NULL COMMENT ‘密码’,
`info` varchar(256) DEFAULT NULL COMMENT ‘自我介绍’,
PRIMARY KEY (`id`)
);
CREATE TABLE `hobbys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` timestamp NOT NULL ,
`update_time` timestamp NOT NULL ,
`version` int(11) NOT NULL DEFAULT ‘0’,
`hobby` varchar(16) DEFAULT NULL COMMENT ‘爱好’,
`student_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
[/code]

[/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]

[code lang=”sql”]
insert into student(id, create_time, update_time, version, name, sex, birthday, password, info)
values(0, now(), now(), 0, ‘测试学生’, ‘male’, ‘1992-01-02’, ‘111111’, ‘您好学校’);
select * from student;
insert into hobbys(id, create_time, update_time, version, hobby, student_id) values (
0, now(), now(), 0, ‘游泳’, 1);
insert into hobbys(id, create_time, update_time, version, hobby, student_id) values (
0, now(), now(), 0, ‘篮球’, 1);

[/code]

[/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]

[code lang=”sql”]
select student.* from student , hobbys where student.id = hobbys.student_id and student.name like ‘%测试%’ and hobbys.hobby =’篮球’;

[/code]

[/pre]
多表查询,from后面是要查询的表,where语句中“student.id = hobbys.student_id”是两个表之间的关联字段相等,like是sql中的模糊查询,”%值%“表示包含值的数据,如果前面%没有表示以值开头,如果后面没有%表示以值结尾。

我们把刚才插入的学生信息的自我介绍改成:”hello world”,

[pre]

[code lang=”sql”]
update student set info = ‘hello world’ where id = 1;

[/code]

[/pre]
更新语句的格式为 update 表名 set 列名1 = ‘值1’, 列名2=‘值2’, … where 列名3 = ‘值3’ and 列名4=‘值4’,一般wher提交为主键筛选,否则会多行更新,容易出现问题。

因为手误,我们执行了两次同一个用户的插入,所以我们需要删除一条,代码如下:

[pre]

[code lang=”sql”]
insert into student(id, create_time, update_time, version, name, sex, birthday, password, info)
values(0, now(), now(), 0, ‘测试学生’, ‘male’, ‘1992-01-02’, ‘111111’, ‘您好学校’);
select * from student ;
delete from student where id = 2;

[/code]

[/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]

[code lang=”java”]
public class StudentPo {
private int id;
private Date createTime;
private Date updateTime;
private int version;
private String name;
private String sex;
private java.sql.Date birthdate;
private String password;
private String info;
}

public class ConnectionUtil {
public static Connection 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();
}
return conn;
}
}
public class StudentInfoDao {
public int insert(StudentPo student) {
Connection conn = ConnectionUtil.getConnection();
int insertRow = 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();
}
return insertRow;
}

public int update(StudentPo student) {
Connection conn = ConnectionUtil.getConnection();
String sql = "update student set name =?, sex = ?, birthday = ?, password = ?, info = ?, update_time = now() where id= ?";
PreparedStatement pstmt = null;
int updateRow = 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();
}
return updateRow;
}

public StudentPo selectStudentPoById(int id) {
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 = new StudentPo();
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"));
}
return studentPo;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}

public List<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();
Lis<StudentPo> studentList = new ArrayList<StudentPo>();
while (rs.next()) {
StudentPo studentPo = new StudentPo();
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);
}
return studentList;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}

public List<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 = new ArrayList<StudentPo>();
while (rs.next()) {
StudentPo studentPo = new StudentPo();
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);
}
return studentList;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}

public int delete(int id) {
Connection conn = ConnectionUtil.getConnection();
String sql = "delete from student where id=?";
PreparedStatement pstmt = null;
int rowNum = 0;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
rowNum = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return rowNum;
}
}
[/code]

[/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]

[code lang=”java”]
public class HobbyPo {
private int id;
private Date createTime;
private Date updateTime;
private int version;
private String hobby;
private int studentId;
}
public class HobbyDao {
public int insert(HobbyPo hobbyPo) {
Connection conn = ConnectionUtil.getConnection();
int insertRow = 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();
}
return insertRow;
}

public int deleteByStudentId(int studentId) {
Connection conn = ConnectionUtil.getConnection();
String sql = "delete from hobbys where student_id=?";
PreparedStatement pstmt = null;
int rowNum = 0;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, studentId);
rowNum = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return rowNum;
}

public List<HobbyPo> selectHobbyPoByStudentId(int studentId) {
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 = new ArrayList<HobbyPo>();
while (rs.next()) {
HobbyPo hobby = new HobbyPo();
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);
}
return hobbyList;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
[/code]

[/pre]
2、添加业务层StudentInfoBo对象,分别实现添加学生信息和展示学生信息列表功能
[pre]

[code lang=”java”]
public List<StudentVo> listAll() {
StudentInfoDao studentInfoDao = new StudentInfoDao();
List<StudentPo> allStudents = studentInfoDao.selectAllStudents();
List<StudentVo> studentVoList = new ArrayList<StudentVo>();
if (allStudents != null &amp;&amp; allStudents.size() > 0) {
HobbyDao hobbyDao = new HobbyDao();
for (StudentPo studentPo : allStudents) {
List<HobbyPo> hobbyPoList = hobbyDao.selectHobbyPoByStudentId(studentPo.getId());
StudentVo studentVo = new StudentVo(studentPo, hobbyPoList);
studentVoList.add(studentVo);
}
}
return studentVoList;
}

public boolean add(StudentVo studentVo) {
StudentInfoDao studentInfoDao = new StudentInfoDao();
List<StudentPo> selectStudentPoByName = studentInfoDao.selectStudentPoByName(studentVo.getName());
if (selectStudentPoByName.size() != 0) {
return false;
}
StudentPo studentPo = new StudentPo(studentVo);
studentInfoDao.insert(studentPo);
List<StudentPo> studentListByName = studentInfoDao.selectStudentPoByName(studentVo.getName());
StudentPo insertStudentPo = studentListByName.get(0);
if (studentVo.getHobbys() != null &amp;&amp; studentVo.getHobbys().length > 0) {
HobbyDao hobbyDao = new HobbyDao();
for (String hobby : studentVo.getHobbys()) {
HobbyPo hobbyPo = new HobbyPo();
hobbyPo.setHobby(hobby);
hobbyPo.setStudentId(insertStudentPo.getId());
hobbyDao.insert(hobbyPo);
}
}
return true;
}
[/code]

[/pre]
查询学生信息列表:先查询学生信息列表,然后根据student.id分别查询爱好信息,并组合成StudentVo进行展示
新增学生信息:根据name查询是否存在,如果存在不进行插入,如果不存在,先插入学生信息,并根据Name查询插入的学生信息获取student.id,然后在分别插入所有的学生爱好信息。
封装了StudentVo以StudentPo和List为入参的构造方法,同时也封装了以StudentPo的以StudentVo为入参的构造方法。
3、修改servlet层,把原来操作ServletContext保存信息的代码修改为使用StudentInfoBo的业务方法实现查询学生信息列表和新增学生信息的功能。

小练习:
实现学生信息的更新和删除。

练习中的代码:
[pre]

[code lang=”java”]
package com.sunhaojie.learntestweb.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
* @ClassName ConnectionUtil
* @Description 链接工具类
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午4:31:02
*/
public class ConnectionUtil {

/**
*
* @Title createConnection
* @Description 创建jdbc链接
* @return Connection
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午4:37:31
*/
public static Connection getConnection() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/student_info?useUnicode=true&amp;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();
}

return conn;
}

}
package com.sunhaojie.learntestweb.po;
import java.util.Date;
/**
* @ClassName HobbyPo
* @Description 学生爱好Po
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午9:54:26
*/
public class HobbyPo {
private int id;
private Date createTime;
private Date updateTime;
private int version;
/**
* 爱好
*/
private String hobby;
/**
* 学生信息id
*/
private int studentId;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
public int getVersion() {
return version;
}
public void setVersion(int version) {
this.version = version;
}
public String getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
public int getStudentId() {
return studentId;
}
public void setStudentId(int studentId) {
this.studentId = studentId;
}
}
package com.sunhaojie.learntestweb.po;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import com.sunhaojie.learntestweb.vo.StudentVo;
/**
* @ClassName StudentPo
* @Description TODO
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午4:44:21
*/
public class StudentPo {
private int id;
private Date createTime;
private Date updateTime;
private int version;
/**
* 名字
*/
private String name;
/**
* 性别
*/
private String sex;
/**
* 生日
*/
private java.sql.Date birthdate;
/**
* 密码
*/
private String password;
/**
* 自我介绍
*/
private String info;
public StudentPo() {
}
public StudentPo(StudentVo studentVo) {
if (studentVo.getYear() != null &amp;&amp; studentVo.getMonth() != null &amp;&amp; studentVo.getDay() != null) {
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
try {
Date birthday = dateFormat.parse(studentVo.getYear() + "-" + studentVo.getMonth() + "-"
+ studentVo.getDay());
this.birthdate = new java.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();
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
public int getVersion() {
return version;
}
public void setVersion(int version) {
this.version = version;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public java.sql.Date getBirthdate() {
return birthdate;
}
public void setBirthdate(java.sql.Date birthdate) {
this.birthdate = birthdate;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getInfo() {
return info;
}
public void setInfo(String info) {
this.info = info;
}
}
package com.sunhaojie.learntestweb.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.sunhaojie.learntestweb.po.HobbyPo;
import com.sunhaojie.learntestweb.utils.ConnectionUtil;
/**
* @ClassName HobbyDao
* @Description 学生爱好信息dao
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午9:56:39
*/
public class HobbyDao {
/**
*
* @Title insert
* @Description 插入爱好信息
* @param hobbyPo
* @return int
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午10:06:44
*/
public int insert(HobbyPo hobbyPo) {
Connection conn = ConnectionUtil.getConnection();
int insertRow = 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();
}
return insertRow;
}
/**
*
* @Title deleteByStudentId
* @Description 根据studentId删除学生爱好信息
* @param studentId
* @return int
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午10:03:51
*/
public int deleteByStudentId(int studentId) {
Connection conn = ConnectionUtil.getConnection();
String sql = "delete from hobbys where student_id=?";
PreparedStatement pstmt = null;
int rowNum = 0;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, studentId);
rowNum = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return rowNum;
}
/**
*
* @Title selectHobbyPoByStudentId
* @Description 根据studentId查询学生爱好信息
* @param studentId
* @return List<HobbyPo>
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午10:02:28
*/
public List<HobbyPo> selectHobbyPoByStudentId(int studentId) {
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 = new ArrayList<HobbyPo>();
while (rs.next()) {
HobbyPo hobby = new HobbyPo();
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);
}
return hobbyList;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
package com.sunhaojie.learntestweb.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.sunhaojie.learntestweb.po.StudentPo;
import com.sunhaojie.learntestweb.utils.ConnectionUtil;

/**
* @ClassName StudentInfoDao
* @Description 学生信息方案
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午4:21:53
*/
public class StudentInfoDao {

/**
*
* @Title insert
* @Description 插入商品信息
* @param student
* @return int
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午5:51:12
*/
public int insert(StudentPo student) {
Connection conn = ConnectionUtil.getConnection();
int insertRow = 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();
}
return insertRow;
}

/**
*
* @Title update
* @Description 更新商品新
* @param student
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午5:51:59
*/
public int update(StudentPo student) {
Connection conn = ConnectionUtil.getConnection();
String sql = "update student set name =?, sex = ?, birthday = ?, password = ?, info = ?, update_time = now() where id= ?";

PreparedStatement pstmt = null;
int updateRow = 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();
}
return updateRow;
}

/**
*
* @Title selectStudentPoById
* @Description 根据id查询
* @param id
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午5:52:22
*/
public StudentPo selectStudentPoById(int id) {
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 = new StudentPo();
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"));
}

return studentPo;
} catch (SQLException e) {
e.printStackTrace();
}

return null;
}

/**
*
* @Title selectStudentPoByName
* @Description 根据名称查询学生信息列表
* @param name
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午6:28:12
*/
public List<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 = new ArrayList<StudentPo>();
while (rs.next()) {
StudentPo studentPo = new StudentPo();
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);
}

return studentList;
} catch (SQLException e) {
e.printStackTrace();
}

return null;
}

/**
*
* @Title selectStudentPoByName
* @Description 查询全部学生信息列表
* @param name
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午6:28:12
*/
public List<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 = new ArrayList<StudentPo>();
while (rs.next()) {
StudentPo studentPo = new StudentPo();
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);
}

return studentList;
} catch (SQLException e) {
e.printStackTrace();
}

return null;
}

/**
*
* @Title delete
* @Description 根据id删除学生信息
* @param id
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午7:28:56
*/
public int delete(int id) {
Connection conn = ConnectionUtil.getConnection();
String sql = "delete from student where id=?";
PreparedStatement pstmt = null;
int rowNum = 0;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
rowNum = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}

return rowNum;
}

public static void main(String[] args) {
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);

// StudentPo studentPo = studentInfoDao.selectStudentPoById(1);
// System.out.println(studentPo.getName());
//
// studentPo.setName("jdbcUpdateName学生");
// studentInfoDao.update(studentPo);

StudentPo student = new StudentPo();
student.setBirthdate(new Date(System.currentTimeMillis()));
student.setName("jdbc测试删除");
student.setSex("female");
student.setPassword("111111");
student.setInfo("hello 世界");
int insert = 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());
}
}
}
package com.sunhaojie.learntestweb.bo;

import java.util.ArrayList;
import java.util.List;

import com.sunhaojie.learntestweb.dao.HobbyDao;
import com.sunhaojie.learntestweb.dao.StudentInfoDao;
import com.sunhaojie.learntestweb.po.HobbyPo;
import com.sunhaojie.learntestweb.po.StudentPo;
import com.sunhaojie.learntestweb.vo.StudentVo;

/**
* @ClassName StudentBo
* @Description 学生信息业务类
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午9:45:03
*/
public class StudentInfoBo {

/**
*
* @Title listAll
* @Description 获取所有学生信息
* @return
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午10:12:05
*/
public List<StudentVo> listAll() {
StudentInfoDao studentInfoDao = new StudentInfoDao();
List<StudentPo> allStudents = studentInfoDao.selectAllStudents();
List<StudentVo> studentVoList = new ArrayList<StudentVo>();

if (allStudents != null &amp;&amp; allStudents.size() > 0) {
HobbyDao hobbyDao = new HobbyDao();

for (StudentPo studentPo : allStudents) {
List<HobbyPo> hobbyPoList = hobbyDao.selectHobbyPoByStudentId(studentPo.getId());
StudentVo studentVo = new StudentVo(studentPo, hobbyPoList);
studentVoList.add(studentVo);
}
}

return studentVoList;
}

/**
*
* @Title add
* @Description 添加学生信息,如果存在studentVo.name则返回false
* @param studentVo
* @return boolean
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月25日 下午10:17:37
*/
public boolean add(StudentVo studentVo) {
StudentInfoDao studentInfoDao = new StudentInfoDao();
List<StudentPo> selectStudentPoByName = studentInfoDao.selectStudentPoByName(studentVo.getName());
if (selectStudentPoByName.size() != 0) {
return false;
}

StudentPo studentPo = new StudentPo(studentVo);
studentInfoDao.insert(studentPo);
List<StudentPo> studentListByName = studentInfoDao.selectStudentPoByName(studentVo.getName());
StudentPo insertStudentPo = studentListByName.get(0);
if (studentVo.getHobbys() != null &amp;&amp; studentVo.getHobbys().length > 0) {
HobbyDao hobbyDao = new HobbyDao();
for (String hobby : studentVo.getHobbys()) {
HobbyPo hobbyPo = new HobbyPo();
hobbyPo.setHobby(hobby);
hobbyPo.setStudentId(insertStudentPo.getId());
hobbyDao.insert(hobbyPo);
}
}

return true;
}
}
package com.sunhaojie.learntestweb.vo;
import java.util.Calendar;
import java.util.List;
import com.sunhaojie.learntestweb.po.HobbyPo;
import com.sunhaojie.learntestweb.po.StudentPo;
/**
* @ClassName StudentVo
* @Description 学生信息
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月23日 下午2:07:48
*/
public class StudentVo {
/**
* 名字
*/
private String name;
/**
* 性别
*/
private String sex;
/**
* 出生年
*/
private String year;
/**
* 出生月
*/
private String month;
/**
* 出生日
*/
private String day;
/**
* 兴趣爱好
*/
private String[] hobbys;
/**
* 密码
*/
private String password;
/**
* 自我介绍
*/
private String info;
public StudentVo() {
}
/**
* @param studentPo
* @param hobbyPoList
*/
public StudentVo(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 &amp;&amp; hobbyPoList.size() > 0) {
String[] hobbys = new String[hobbyPoList.size()];
for (int i = 0; i < hobbyPoList.size(); i++) {
hobbys[i] = hobbyPoList.get(i).getHobby();
}
this.hobbys = hobbys;
}
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
public String getMonth() {
return month;
}
public void setMonth(String month) {
this.month = month;
}
public String getDay() {
return day;
}
public void setDay(String day) {
this.day = day;
}
public String[] getHobbys() {
return hobbys;
}
public void setHobbys(String[] hobbys) {
this.hobbys = hobbys;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getInfo() {
return info;
}
public void setInfo(String info) {
this.info = info;
}
}
package com.sunhaojie.learntestweb.web;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.velocity.Template;
import org.apache.velocity.context.Context;
import org.apache.velocity.tools.view.VelocityViewServlet;
import com.sunhaojie.learntestweb.bo.StudentInfoBo;
import com.sunhaojie.learntestweb.vo.StudentVo;
/**
* @ClassName StudentListServlet
* @Description 学生信息列表
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月23日 下午4:33:28
*/
public class StudentListServlet extends VelocityViewServlet {
@Override
protected Template handleRequest(HttpServletRequest request, HttpServletResponse response, Context ctx) {
StudentInfoBo studentInfoBo = new StudentInfoBo();
List<StudentVo> studentList = studentInfoBo.listAll();
ctx.put("studentList", studentList);
return getTemplate("studentList.vm");
}
}
package com.sunhaojie.learntestweb.web;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.sunhaojie.learntestweb.bo.StudentInfoBo;
import com.sunhaojie.learntestweb.vo.StudentVo;
/**
* @ClassName SubmitStudentInfoServlet
* @Description 学生信息提交
*
* @author sunhaojie 3113751575@qq.com
* @date 2016年2月23日 下午2:02:08
*/
public class SubmitStudentInfoServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@SuppressWarnings("unchecked")
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, 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 = new StudentVo();
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 = new StudentInfoBo();
boolean flag = studentInfoBo.add(studentVo);
resp.setContentType("text/html; charset=UTF-8");
if (flag == false) {
resp.getWriter().print(("学生名称已存在:" + studentVo.getName()));
return;
}
resp.sendRedirect("studentList");
}
}
[/code]

[/pre]