对数据库表的增删改查

对数据库表的增删改查

1、做一个User表

 create table user(
    -> id int not null,
    -> username varchar(20) not null,
    -> password varchar(20) not null,
    -> roleld varchar(20));

2、所建表如下所示：

表tb7：

tb7中具体内容

util包:

package com.openleb.utils;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public abstract class JDBCutils {
	
	private String driver = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://localhost:3306/demo1";
	private static String username = "root";
	private static String password = "123456";
	private Connection conn = null;
	private Statement st = null;
	private ResultSet rs = null;
	
	//因为加载驱动这件事 在程序启动的时候就被加载且只执行一次
	static{
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}
	}
	//获取连接对象
	public static Connection getConnection() throws SQLException{
		return DriverManager.getConnection(url, username,password);
	}
	
	//添加 修改 删除
	public void update(String sql){
		try{
			conn = getConnection();
			st = conn.createStatement();
			int bret = st.executeUpdate(sql);
			System.out.println(bret);
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			JDBCclose(conn,st);
		}
	}
	
	//多条记录的查询 List ArrayList
	
	public List queryByAll(String sql){
		List list = null;
		try{
			conn = getConnection();
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			list = rsToList(rs);
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			JDBCclose(conn,st);
		}
		return list;
	}
	
	//单条记录的查询  Object
	
	public Object queryByOne(String sql){
		Object obj = null;
		try{
			conn = getConnection();
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			obj = rsToObj(rs);
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			JDBCclose(conn,st);
		}
		return obj;
	}
	public abstract Object rsToObj(ResultSet rs);
	public abstract List rsToList(ResultSet rs);
	
	public static void JDBCclose(Connection conn, Statement st) {
		try{
			st.close();
			if(st != null){
				st = null;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try{
				conn.close();
				if(conn != null){
					conn = null;
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		
	}

	/*
	 * 释放资源
	 * mysql的连接资源 比较珍稀的资源
	 * 尽晚的获取连接，尽早释放资源
	*/
	public static void JDBCclose(Connection conn,Statement st,ResultSet rs){
		try{
			rs.close();
			if(rs != null){
				rs = null;
			}
				
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try{
				st.close();
				if(st != null){
					st = null;
				}
				
			}catch(SQLException e){
				e.getSQLState();
			}finally{
				try{
					conn.close();
					if(conn != null){
						conn = null;
					}
					
				}catch(SQLException e){
					e.getSQLState();
				}
			}
		}
	}
}

dao包：

package com.openleb.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
//数据链路层
import com.openleb.pojo.Employee;
import com.openleb.utils.JDBCutils;

public class EmployeeServiceDao extends JDBCutils{

	@Override
	public Object rsToObj(ResultSet rs) {
		Object obj = null;
		try {
			Employee emp = new Employee();
			if(rs.next()){
				emp.setId(rs.getString("id"));
				emp.setUsername(rs.getString("username"));
				emp.setSalary(rs.getDouble("salary"));
				emp.setAge(rs.getInt("age"));
				emp.setDepart(rs.getString("depart"));
				obj = emp;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public List rsToList(ResultSet rs) {
		
		List list = new ArrayList();
		try {
			Employee emp = new Employee();
			while(rs.next()){
				emp.setId(rs.getString("id"));
				emp.setUsername(rs.getString("username"));
				emp.setSalary(rs.getDouble("salary"));
				emp.setAge(rs.getInt("age"));
				emp.setDepart(rs.getString("depart"));
				list.add(emp);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}

pojo包：

package com.openleb.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
//数据链路层
import com.openleb.pojo.Employee;
import com.openleb.utils.JDBCutils;

public class EmployeeServiceDao extends JDBCutils{

	@Override
	public Object rsToObj(ResultSet rs) {
		Object obj = null;
		try {
			Employee emp = new Employee();
			if(rs.next()){
				emp.setId(rs.getString("id"));
				emp.setUsername(rs.getString("username"));
				emp.setSalary(rs.getDouble("salary"));
				emp.setAge(rs.getInt("age"));
				emp.setDepart(rs.getString("depart"));
				obj = emp;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public List rsToList(ResultSet rs) {
		
		List list = new ArrayList();
		try {
			Employee emp = new Employee();
			while(rs.next()){
				emp.setId(rs.getString("id"));
				emp.setUsername(rs.getString("username"));
				emp.setSalary(rs.getDouble("salary"));
				emp.setAge(rs.getInt("age"));
				emp.setDepart(rs.getString("depart"));
				list.add(emp);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}

servic包：

package com.openleb.servic;

import com.openleb.pojo.Employee;

import java.util.List;

public interface EmployeeService {
    public Employee queryById(String id);
    public List queryByUsername(String username);


}

impl包:

package com.openleb.service.impl;

import com.openleb.dao.EmployeeServiceDao;
import com.openleb.pojo.Employee;
import com.openleb.servic.EmployeeService;

import java.util.List;

public class EmployeeServiceImpl implements EmployeeService {

    EmployeeServiceDao edao = new EmployeeServiceDao();
    @Override
    public Employee queryById(String id) {
        String sql = "select * from tb7 where id = '"+id+"'";
        Object obj = edao.queryByOne(sql);
        Employee em = (Employee) obj;
        edao.queryByOne(sql);
        return em;
    }

    @Override
    public List queryByUsername(String username) {
        String sql = "select * from tb7 where username = '"+username+"'";
        List list = (List) edao.queryByAll(sql);
        return list;
    }
}

text包:

package com.openleb.test;

import java.util.List;

import org.junit.Test;

import com.openleb.pojo.Employee;
import com.openleb.service.impl.EmployeeServiceImpl;

import junit.framework.TestCase;

public class EmployeeServiceImplText {
	EmployeeServiceImpl eimpl = new EmployeeServiceImpl();
	@Test
	public void testQueryById() {
		Employee e = eimpl.queryById("0005");
		System.out.println(e);
		TestCase.assertEquals("guiqi", e.getUsername());
		
	}
	
	@Test
	public void testQueryByUserName(){
		List list = eimpl.queryByUsername("guiqi");
		System.out.println(list);
		TestCase.assertEquals(2, list.size());
		
	}

}

运行结果：

Employee[id=0005,username=guiqi,salary=7000.0,age=22,depart=Meigong]
[Employee[id=006,username=guiqi,salary=7000.0,age=32,depart=Meigong], Employee[id=006,username=guiqi,salary=7000.0,age=32,depart=Meigong]]