import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner;

import org.junit.Test;

import com.mysql.jdbc.Connection;

public class Test5 {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
/**
* 修改学生信息
*/
@Test
public void update(){
int sNo= 0;
list();
System.out.println("请输入要修改的编号：");
Scanner scanner = new Scanner(System.in);
sNo = scanner.nextInt();
conn = ConnectionSql.getConnection();
String sql = "select * from student where sNo = ?";
String sql1 = "update student set sName=?,sSex=?,sClass=?,sBirthday=? where sNo=?";
try {
pst = conn.prepareStatement(sql);
pst.setInt( 1, sNo);
ResultSet rs = pst.executeQuery();
getStudentResultSet(rs);
System.out.println("请输入修改信息：");
pst = conn.prepareStatement(sql1);
inputInfo( conn, pst, sql1);
} catch (SQLException e) {
e.printStackTrace();
}
}

/**
* 添加学生
*/
// @Test
public void add(){
list();
conn = ConnectionSql.getConnection();
String sql = "insert into student values ( ?,?,?,?,?)";
System.out.println("请输入要添加的学生信息：");
Scanner sc = new Scanner(System.in);
String str = sc.nextLine();
String strs[] = str.split(" ");
try {
pst = conn.prepareStatement(sql);
pst.setInt(1,Integer.parseInt(strs[0]));
pst.setString(2, strs[1]);
pst.setString(3, strs[2]);
pst.setString(4, strs[3]);
pst.setString(5, strs[4]);
pst.execute();
list();
} catch (SQLException e) {
e.printStackTrace();
}
}

/**
*
* @param conn
* @param pst
* @param sql
*/
public void inputInfo(Connection conn, PreparedStatement pst,String sql){
Scanner sc = new Scanner(System.in);
String str = sc.nextLine();
String strs[] = str.split(" ");
try {

pst.setString(1, strs[1]);
pst.setString(2, strs[2]);
pst.setString(3, strs[3]);
pst.setString(4, strs[4]);
pst.setInt(5,Integer.parseInt(strs[0]));
pst.execute();
list();

} catch (SQLException e) {
e.printStackTrace();
}finally{
ConnectionSql.getClose(conn, pst, rs);
}
}

/**
* 删除学生信息
*/
// @Test
public void delete(){
list();
System.out.println("请输入要删除的编号：");
Scanner scanner = new Scanner(System.in);
int sNo = scanner.nextInt();
conn = ConnectionSql.getConnection();
String sql = "delete from student where sNo = ?";
try {
pst = conn.prepareStatement(sql);
pst.setInt(1, sNo);
pst.execute();
list();
} catch (SQLException e) {
e.printStackTrace();
}

}

/**
* 学生列表
*/
//@Test
public void list(){
conn = ConnectionSql.getConnection();
String sql = "select * from student where 1=1";
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
getStudentResultSet(rs);

} catch (SQLException e) {
e.printStackTrace();
}finally{
ConnectionSql.getClose(conn, pst, rs);
}
}

/**
* 根据 结果集遍历学生信息
* @param rs
*/
public void getStudentResultSet(ResultSet rs){
try {
System.out.println("当前学生列表：");
while( rs.next()){
int sNo = rs.getInt(1);
String sName = rs.getString(2);
String sSex = rs.getString(3);
String sClass = rs.getString(4);
String sBirthday = rs.getString(5);

System.out.println(sNo + " " + sName + " " + sSex + " " + sClass + " " + sBirthday );
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

/**
* 数据库
* @author Lenovo
*
*/
class ConnectionSql{
static final String DRIVER = "com.mysql.jdbc.Driver";
static final String USER = "root";
static final String PASS = "123456";
static final String URL = "jdbc:mysql://localhost:3306/chenshuilan?useUnicode=true&characterEncoding=utf-8";

//连接数据库
public static Connection getConnection(){
Connection conn= null;
try {
Class.forName(DRIVER);
// System.out.println("链接数据库");
conn = (Connection) DriverManager.getConnection(URL, USER, PASS);
// System.out.println("链接数据库成功");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}

//关闭数据库
public static void getClose(Connection conn, PreparedStatement pst, ResultSet rs){
try {
if ( conn != null){
conn.close();
}

if ( pst != null){
pst.close();
}
if ( rs != null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}