12.3
今天练习写一个web系统，学生成绩管理系统，这个系统特别大，加上现在对web系统还不是特别熟悉，今天写的不是很多，在写的过程中没有遇到问题，明天计划继续写这个web系统。
代码部分：
package util;
//数据库连接
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 数据库连接工具
* @author Zheng
*
*/
public class DBUtil {
public static String db_url = "jdbc:mysql://localhost:3306/user?useSSL=false";
public static String db_user = "root";
public static String db_pass = "123456";
public static Connection getConn () {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");//加载驱动
conn = DriverManager.getConnection(db_url, db_user, db_pass);//通过输入的用户名还有密码来建立一个到数据库的连接主要是调用connect（）方法
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭连接
* @param state
* @param conn
*/
public static void close (Statement state, Connection conn) {
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close (ResultSet rs, Statement state, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package com.newjavaweb;
public class User {
private String id;
private String name;
private String clas;
private String ke;
private String score;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getClas() {
return clas;
}
public void setClas(String clas) {
this.clas = clas;
}
public String getKe() {
return ke;
}
public void setKe(String ke) {
this.ke = ke;
}
public String getScore() {
return score;
}
public void setScore(String score) {
this.score = score;
}
public User(String id, String name, String clas, String ke, String score) {
super();
this.id = id;
this.name = name;
this.clas = clas;
this.ke = ke;
this.score = score;
}
}
package Dao;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import util.DBUtil;
import com.newjavaweb.User;
public class userdao {
public boolean add(User user) {
String sql = "insert into text(id,name,clas,ke,score)" + "values('" + user.getId() + "','" + user.getName()+ "','" + user.getClas() + "','" + user.getKe()+ "','" + user.getScore()+ "')";
Connection conn = DBUtil.getConn();//调用方法连接数据库
Statement state = null;//创建一个Statement对象来执行静态的SQL语句
//Statement用于在已经建立数据库连接上发送静态的SQL语句
boolean f = false;
int a = 0 ;
try { //监视大括号内的代码
state = conn.createStatement();//上面的是详解
a = state.executeUpdate(sql);
//执行指定的更新类的SQl语句，并返回操作结果的整型值
} catch (Exception e) { //捕获错误
e.printStackTrace();
} finally {
//关闭z 连接
DBUtil.close(state, conn);
}
if (a > 0) {
f = true;
}
return f;
}
public static List<User> loadview(String sql) {
// TODO Auto-generated method stub
String sql1="select * from text "+ sql;
List<User> list =new ArrayList<User>();
Connection con=null;
PreparedStatement psts=null;
ResultSet rs=null;
try
{
con=DBUtil.getConn();
//String sql="select * from course";
psts=con.prepareStatement(sql1);
rs=psts.executeQuery();//记录返回的结果
while(rs.next())
{
String id=rs.getString("id");
String name=rs.getString("name");
String clas=rs.getString("clas");
String ke=rs.getString("ke");
String score=rs.getString("score");
User yi=new User(id,name,clas,ke,score);
list.add(yi);
}
DBUtil.close(rs, psts, con);
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return list;
}
//return null;
public String find(String name)
{
String sql = "select * from text where ";
if (name!= "") {
sql += "name like '" + name +"'";
}
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
String username2="@";
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
username2 = rs.getString("name");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return username2;
}
public String search(String name) {
String sql = "select * from text where ";
if (name!= "") {
sql += "name like '%" + name + "%'";
}
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
String name2 = "@";
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
name2 = rs.getString("name");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return name2;
}
public String search1(String id, String name, String clas, String ke, String score) {
String sql = "select * from text where ";
if (id!= "") {
sql += "id like '%" + id + "%'";
}
else if(name!="")
{
sql += "name like '%" + name + "%'";
}
else if(clas!="")
{
sql += "clas like '%" + clas + "%'";
}
else if(ke!="")
{
sql += "ke like '%" + ke + "%'";
}
else if(score!="")
{
sql += "score like '%" + score + "%'";
}
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
String name2 = "@";
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
name2 = rs.getString("name");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return name2;
}
public List<User> examine(String name1) throws ServletException, IOException {
String sql = "select * from text where ";
if (name1!= "") {
sql += "name like '%" + name1 + "%'";
}
List<User> list1 = new ArrayList<>();
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
String id=rs.getString("id");
String name=rs.getString("name");
String clas=rs.getString("clas");
String ke=rs.getString("ke");
String score=rs.getString("score");
User bean2 = new User(id,name,clas,ke,score);
list1.add(bean2);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return list1;
}
public List<User> theall() {
String sql = "select * from text";//全部
List<User> list = new ArrayList<>();
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
User bean = null;
if(rs!=null) {
while (rs.next()) {
String id=rs.getString("id");
String name=rs.getString("name");
String clas=rs.getString("clas");
String ke=rs.getString("ke");
String score=rs.getString("score");
bean = new User(id,name,clas,ke,score);
list.add(bean);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return list;
}
public boolean delete (String name) {
boolean f = false;
String sql = "delete from text where name='" + name + "'";
Connection conn = DBUtil.getConn();
Statement state = null;
int a = 0;
try {
state = conn.createStatement();
a = state.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
finally {
DBUtil.close(state, conn);
}
if (a > 0) {
f = true;
}
return f;
}
public User getUserbyzhanghu(String name5) {
String sql = "select * from text where name ='" + name5 + "'";
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
User user3 = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
String id=rs.getString("id");
String name=rs.getString("name");
String clas=rs.getString("clas");
String ke=rs.getString("ke");
String score=rs.getString("score");
user3= new User(id,name,clas,ke,score);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return user3;
}
public boolean update(User user,String zh)
{
String sql="update text set id='" + user.getId() + "',name='" + user.getName() + "',clas='" + user.getClas() + "',ke='" + user.getKe() + "',score='" + user.getScore() + "' where name='" + zh + "'";
Connection conn = DBUtil.getConn();
Statement state = null;
boolean f = false;
int a = 0;
try {
state = conn.createStatement();
a = state.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(state, conn);
}
if (a > 0) {
f = true;
}
return f;
}
public List<User> paixu(){
String sql = "select * from text";//全部
List<User> list = new ArrayList<>();
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
User bean = null;
if(rs!=null) {
while (rs.next()) {
String id=rs.getString("id");
String name=rs.getString("name");
String clas=rs.getString("clas");
String ke=rs.getString("ke");
String score=rs.getString("score");
bean = new User(id,name,clas,ke,score);
list.add(bean);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return list;
}
}