javaweb增删改查练习
DB.java:
package com.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DB {
private static String mysqlname = "database";//数据库名
private static Connection con;
private static Statement stm;
private static ResultSet rs;
private static String coursename = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1:3306/"+mysqlname+"?useUnicode=true&characterEncoding=GB18030&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true";
public static Connection getCon() {
try {
Class.forName(coursename);
System.out.println("注册驱动成功");
}catch(ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url,"root","20000604");
System.out.println("建立连接成功");
}catch(Exception e){
e.printStackTrace();
con = null;
}
return con;
}
public static void close(Statement stm,Connection connection) {
if(stm!=null) {
try {
stm.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
if(connection!=null) {
try {
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
//关闭
public static void close(ResultSet rs,Statement stm,Connection connection) {
if(rs!=null) {
try {
rs.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
if(stm!=null) {
try {
stm.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
if(connection!=null) {
try {
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args)
{
getCon();
}
}
Dao.java:
package com.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import com.bean.staff;
import com.db.DB;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class Dao {
//根据名称进行查询
@SuppressWarnings("static-access")
public static staff selectName_staff(String staff_name) {
String tablename = "staff";
System.out.println("select staff where name = "+staff_name);
staff sta=null;
DB db=new DB();
Connection con = (Connection) db.getCon();
try {
Statement stm = con.createStatement();
ResultSet rs = stm.executeQuery("select * from "+tablename+" where name='" + staff_name + "'");
if(rs.next())
{
sta = new staff();
System.out.println("select the stu from mysql");
sta.setJobid(rs.getString("jobid"));
sta.setName(rs.getString("name"));
sta.setSex(rs.getString("sex"));
sta.setBirthday(rs.getString("birthday"));
sta.setDepartment(rs.getString("department"));
sta.setRole(rs.getString("role"));
sta.setPassword(rs.getString("password"));
//bean.setValue2(Integer.parseInt(rs.getString("teacher")));
//bean.setValue3(Boolean.parseBoolean(rs.getString("address")));
System.out.println("name of the stu is "+rs.getString("staff_name"));
}
db.close(rs,stm, con);
}catch(Exception e) {
e.printStackTrace();
}
return sta;
}
//添加普通员工信息
@SuppressWarnings("static-access")
public boolean add_staff(staff staff) {
String tablename = "staff";//表名
DB db=new DB();
Connection con = (Connection) db.getCon();
try {
String sql="insert into "+tablename+"(jobid,name,sex,birthday,department,role,password) values ('"+staff.getJobid()+"','"+staff.getName()+"','"+staff.getSex()+"','"+staff.getBirthday()+"','"+staff.getDepartment()+"','"+staff.getRole()+"','"+staff.getPassword()+"')";
Statement stm = con.createStatement();
System.out.println(sql);
stm.execute(sql);
db.close(stm, con);
}catch(Exception e) {
e.printStackTrace();
System.out.println("add false");
return false;
}
System.out.println("add true");
return true;
}
//根据员工名字进行删除
@SuppressWarnings("static-access")
public static boolean delete_staff(String name)
{
//String tablename ="test";
DB db=new DB();
Connection con = (Connection) db.getCon();
//Connection con=null;
PreparedStatement stm=null;
try {
con=(Connection) DB.getCon();
String sql="delete from staff where name='"+name+"'";
//String sql="delete from test where name='"+bean.getName()+"'";
System.out.println(sql);
stm=(PreparedStatement) con.prepareStatement(sql);
stm.executeUpdate();
return true;
}
catch(SQLException e)
{
e.printStackTrace();
}
finally {
db.close(stm, con);
}
return false;
}
//根据员工名字进行修改
@SuppressWarnings("static-access")
public boolean update_staff(staff stu) {
Connection con=null;
PreparedStatement stm=null;
String tablename="staff";
DB db=new DB();
try {
con=(Connection) DB.getCon();
//String sql="update "+tablename+" set teacher=?,classroom=? where name=?";
String sql="update "+tablename+" set jobid=?,sex=?,birthday=?,department=?,role=?,password=? where name=?";
//String sql="update "+tablename+"set teacher=?,classroom=? where name=?";
stm=(PreparedStatement) con.prepareStatement(sql);
//stm.setString(3,bean.getName());
//stm.setString(1, bean.getTeacher());
//stm.setString(2,bean.getClassroom());
stm.setString(1,stu.getJobid());
stm.setString(2,stu.getSex());
stm.setString(3,stu.getBirthday());
stm.setString(4,stu.getDepartment());
stm.setString(5,stu.getRole());
stm.setString(6,stu.getPassword());
stm.setString(6,stu.getName());
stm.executeUpdate();
}
catch(SQLException e)
{
e.printStackTrace();
return false;
}
finally {
db.close(stm, con);
return true;
}
}
//浏览员工全部信息
@SuppressWarnings("static-access")
public ArrayList<staff> selectList_staff(){
Connection con=null;
PreparedStatement stm=null;
ResultSet rs=null;
ArrayList<staff> listbean=new ArrayList<staff>();
String tablename="staff";
DB db=new DB();
try {
con=(Connection) db.getCon();
String sql="select * from "+tablename;
stm=(PreparedStatement) con.prepareStatement(sql);
rs=stm.executeQuery();
while(rs.next()){
String jobid=rs.getString("jobid");
String name=rs.getString("name");
String sex=rs.getString("sex");
String birthday=rs.getString("birthday");
String department=rs.getString("department");
String role=rs.getString("role");
String password=rs.getString("password");
staff stu=new staff(0,jobid,name,sex,birthday,department,role,password);
listbean.add(stu);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
db.close(stm, con);
}
return listbean;
}
}
在这里遇到最多的问题是dao.java中对sql语句的书写，这也是出现问题最多的地方，希望自己更加的熟练，不断坚持，加油。
