JAVABean 和preparedstatment的相关使用

JavaBean

Dao层数据链路层

package com.openlab.dao;

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

public class EmployeeServcieDao 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.openlab.pojo;
//领域对象层
public class Employee {
	
	private String id;
	private String username;
	private double salary;
	private int age;
	private String depart;
	
	public Employee() {
		// TODO Auto-generated constructor stub
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public double getSalary() {
		return salary;
	}

	public void setSalary(double salary) {
		this.salary = salary;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getDepart() {
		return depart;
	}

	public void setDepart(String depart) {
		this.depart = depart;
	}

	@Override
	public String toString() {
		return "Employee [id=" + id + ", username=" + username + ", salary=" + salary + ", age=" + age + ", depart="
				+ depart + "]";
	}
	
	

}

JDBCutil

package com.openlab.JDBCUtils;

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

import com.openlab.pojo.Employee;

public abstract class JDBCUTIL{
	private String driver = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://localhost:3306/demo";
	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) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取连接对象
	 * @return
	 * @throws SQLException
	 */
	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) {
			// TODO Auto-generated catch block
			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) {
			// TODO Auto-generated catch block
			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) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			jdbcClose(conn, st);
		}
		
		return obj;
	}
	
	
	
	/**
	 * 释放资源
	 * mysql的连接资源 比较珍稀的资源
	 * 尽晚的获取连接，尽早释放资源
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public static void jdbcClose(Connection conn,Statement st,ResultSet rs){
		
		try {
			rs.close();
			if(rs != null){
				rs = null;
		    }
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			try {
				st.close();
				if(st!=null){
					st = null;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
				conn.close();
				if(conn!=null){
					conn = null;
				}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	public static void jdbcClose(Connection conn,Statement st){
		
		try {
			st.close();
			if(st!=null){
				st = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
			conn.close();
			if(conn!=null){
				conn = null;
			}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public abstract List RstoList(ResultSet rs);
	public abstract Object RstoObj(ResultSet rs);
	
	
	

}

service接口

package com.openlab.Service;

import java.sql.ResultSet;
import java.util.List;

import com.openlab.JDBCUtils.JDBCUTIL;
import com.openlab.pojo.Employee;

public interface Employeeservice{
	public Employee queryByid(String id);
	public List queryByusername(String username);
	
}

service实现层

package com.openlab.serviceimpl;

import java.util.List;

import com.openlab.JDBCUtils.JDBCUTIL;
import com.openlab.Service.Employeeservice;
import com.openlab.dao.EmployeserviceDao;
import com.openlab.pojo.Employee;

public class EmployeeServiceimpl implements Employeeservice{
	EmployeserviceDao  dao = new EmployeserviceDao();
	@Override
	public Employee queryByid(String id) {
		// TODO Auto-generated method stub
		String sql =  "select * from tb7 where id='"+id+"'";
		Employee e = (Employee) dao.queryByOne(sql);
		return e;
	}

	@Override
	public List queryByusername(String username) {
		// TODO Auto-generated method stub
		String sql ="select * from tb7 where username='"+username+"'";
		List list =  dao.queryByAll(sql);
		return list;
	}

}

PreparedStatment相关实现

package com.openlab.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import com.openlab.utils.JDBCUtils;

public class Preparedtest {
	public static void main(String[] args) {
		Connection conn =null;
		PreparedStatement ps = null;
		Statement st = null;
		String sql = "update  tb7 set salary = salary + 20000 where id ='006'";
		String sql1 ="update tb7 set salary  = salary +20000 where id =?";
		 try {
			/*  第一种跟方式使用statement 方式跟新 不安全
			 conn = JDBCUtils.getConnection();//链接数据库
			st = conn.createStatement();
			int a = st.executeUpdate(sql);
			System.out.println(a);*/
//			 第二种 方式  使用preparestatment 预处理方式进行跟新  更加安全
			 conn = JDBCUtils.getConnection();
			 ps=conn.prepareStatement(sql1);//直接进行预处理
			 ps.setString(1, "7");
			 int b = ps.executeUpdate();
			 System.out.println(b);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}