package com. ithei. jdbc. dao ;
import com. ithei. jdbc. domian. Student ;
import java. sql. * ;
import java. text. SimpleDateFormat ;
import java. util. ArrayList ;
public class StudentDaoImpl implements StudentDao {
@Override
public ArrayList < Student > findall ( ) {
ArrayList < Student > list = new ArrayList < > ( ) ;
Connection connection = null ;
Statement statement = null ;
ResultSet resultSet = null ;
try {
Class . forName ( "com.mysql.jdbc.Driver" ) ;
connection = DriverManager . getConnection ( "jdbc:mysql://127.0.01:3306/db11" , "root" , "root" ) ;
statement = connection. createStatement ( ) ;
String s = "SELECT *FROM Student" ;
resultSet = statement. executeQuery ( s) ;
while ( resultSet. next ( ) ) {
int id = resultSet. getInt ( "sid" ) ;
String name = resultSet. getString ( "name" ) ;
int age = resultSet. getInt ( "age" ) ;
Date birthday = resultSet. getDate ( "birthday" ) ;
Student stu = new Student ( ) ;
stu. setSid ( id) ;
stu. setName ( name) ;
stu. setAge ( age) ;
stu. setBirthday ( birthday) ;
list. add ( stu) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
if ( connection != null ) {
try {
connection. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( statement != null ) {
try {
statement. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( resultSet != null ) {
try {
resultSet. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
return list;
}
}
@Override
public Student findByid ( Integer id) {
Student st = new Student ( ) ;
Connection connection = null ;
PreparedStatement pstm = null ;
ResultSet resultSet = null ;
try {
Class . forName ( "com.mysql.jdbc.Driver" ) ;
connection = DriverManager . getConnection ( "jdbc:mysql://127.0.01:3306/db11" , "root" , "root" ) ;
pstm = connection. prepareStatement ( "select * from student where sid = ? " ) ;
pstm. setInt ( 1 , id) ;
resultSet = pstm . executeQuery ( ) ;
while ( resultSet. next ( ) ) {
int sid = resultSet. getInt ( "sid" ) ;
String name = resultSet. getString ( "name" ) ;
int age = resultSet. getInt ( "age" ) ;
Date birthday = resultSet. getDate ( "birthday" ) ;
st. setSid ( id) ;
st. setName ( name) ;
st. setAge ( age) ;
st. setBirthday ( birthday) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
if ( connection != null ) {
try {
connection. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( pstm != null ) {
try {
pstm. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( resultSet != null ) {
try {
resultSet. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
return st;
}
}
@Override
public int Added ( Student stu) {
Connection connection = null ;
PreparedStatement pepe = null ;
int resultSet = 0 ;
try {
Class . forName ( "com.mysql.jdbc.Driver" ) ;
connection = DriverManager . getConnection ( "jdbc:mysql://127.0.01:3306/db11" , "root" , "root" ) ;
pepe = connection. prepareStatement ( "insert into student(sid,name,age,birthday)values(null,?,?,?)" ) ;
java. util. Date date = stu. getBirthday ( ) ;
SimpleDateFormat simpleDateFormat = new SimpleDateFormat ( "yyyy-MM-dd" ) ;
String format = simpleDateFormat. format ( date) ;
pepe. setString ( 1 , stu. getName ( ) ) ;
pepe. setInt ( 2 , stu. getAge ( ) ) ;
pepe. setString ( 3 , format) ;
resultSet = pepe . executeUpdate ( ) ;
} catch (
Exception e)
{
e. printStackTrace ( ) ;
} finally
{
if ( connection != null ) {
try {
connection. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( pepe!= null ) {
try {
pepe. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
return resultSet;
}
@Override
public int update ( Student stu) {
Connection connection = null ;
Statement statement = null ;
int resultSet = 0 ;
try {
Class . forName ( "com.mysql.jdbc.Driver" ) ;
connection = DriverManager . getConnection ( "jdbc:mysql://127.0.01:3306/db11" , "root" , "root" ) ;
statement = connection. createStatement ( ) ;
java. util. Date date = stu. getBirthday ( ) ;
SimpleDateFormat simpleDateFormat = new SimpleDateFormat ( "yyyy-MM-dd" ) ;
String format = simpleDateFormat. format ( date) ;
String s = "UPDATE Student SET sid ='" + stu. getSid ( ) + "',name ='" + stu. getName ( ) + "',age ='" + stu. getAge ( ) + "',birthday ='" + format+ "' WHERE sid='" + stu. getSid ( ) + "'" ;
resultSet = statement. executeUpdate ( s) ;
} catch (
Exception e)
{
e. printStackTrace ( ) ;
} finally
{
if ( connection != null ) {
try {
connection. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( statement != null ) {
try {
statement. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
return resultSet;
}
@Override
public int delete ( Integer id) {
Connection connection = null ;
Statement statement = null ;
int resultSet = 0 ;
try {
Class . forName ( "com.mysql.jdbc.Driver" ) ;
connection = DriverManager . getConnection ( "jdbc:mysql://127.0.01:3306/db11" , "root" , "root" ) ;
statement = connection. createStatement ( ) ;
String s = "DELETE FROM Student WHERE sid ='" + id+ "' " ;
resultSet = statement. executeUpdate ( s) ;
} catch (
Exception e)
{
e. printStackTrace ( ) ;
} finally
{
if ( connection != null ) {
try {
connection. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( statement != null ) {
try {
statement. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
return resultSet;
}
}
public class StudentSevletImpl implements StudentServlet {
private StudentDao stu1 = new StudentDaoImpl ( ) ;
@Override
public ArrayList < Student > findall ( ) {
return stu1. findall ( ) ;
}
@Override
public Student findByid ( Integer id) {
return stu1. findByid ( id) ;
}
@Override
public int Added ( Student stu) {
return stu1. Added( stu) ;
}
@Override
public int update ( Student stu) {
return stu1. update ( stu) ;
}
@Override
public int delete ( Integer id) {
return stu1. delete ( id) ;
}
}
public class StudentController {
private StudentServlet sevlet = new StudentSevletImpl ( ) ;
@Test
public void findall ( ) {
ArrayList < Student > list = sevlet. findall ( ) ;
for ( Student student : list) {
System . out. println ( student) ;
}
}
@Test
public void findByid ( ) {
Student byid = sevlet. findByid ( 3 ) ;
System . out. println ( byid) ;
}
@Test
public void Added ( ) {
Student st = new Student ( null , "关羽" , 40 , new Date ( ) ) ;
int added = sevlet. Added( st) ;
if ( added!= 0 ) {
System . out. println ( "添加成功" ) ;
} else {
System . out. println ( "添加失败" ) ;
}
}
@Test
public void update ( ) {
Student stu = sevlet. findByid ( 5 ) ;
stu. setName ( "武则天" ) ;
stu. setAge ( 30 ) ;
int update = sevlet. update ( stu) ;
if ( update!= 0 ) {
System . out. println ( "修改成功" ) ;
} else {
System . out. println ( "修改失败" ) ;
}
}
@Test
public void delete ( ) {
int delete = sevlet. delete ( 5 ) ;
if ( delete!= 0 ) {
System . out. println ( "删除成功" ) ;
} else {
System . out. println ( "删除失败" ) ;
}
}
}