JDBC优化-Mysql Tutorial-php.cn

一.什么是dao 二.dao模式实现三.dao优化定义包的模式如下： TecDao package dao;import java.sql.SQLException;import java.util.List;import pojo.TecPojo;public interface TecDao {public int insert(TecPojo tec)throws SQLException;public int delet

一.什么是dao

二.dao模式实现

三.dao优化

定义包的模式如下：

TecDao

package dao;

import java.sql.SQLException;
import java.util.List;

import pojo.TecPojo;

public interface TecDao {
	public int insert(TecPojo tec)throws SQLException;
	public int delete(int id)throws SQLException;
	public int update(TecPojo tec)throws SQLException;
	public List<TecPojo> query(TecPojo tec)throws SQLException;
}

Copy after login

TecDaoImpl

package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import dao.TecDao;
import pojo.TecPojo;
import util.SQLUtil;

public class TecDaoImpl implements TecDao{
	

	public int insert(TecPojo tec) throws SQLException {
		Connection conn = SQLUtil.getConnection();
		String sql = "insert into tec (name,age,gender,job,createDate)values(?,?,?,?,?)";
		PreparedStatement state = conn.prepareStatement(sql);
		state.setString(1, tec.getName());
		state.setInt(2, tec.getAge());
		state.setString(3, tec.getGender());
		state.setString(4, tec.getJob());
		state.setString(5, tec.getCreateDate());
		int result = state.executeUpdate();
		if(result>0){
			System.out.println("Yes");
		}
		return result;
	}


	public int update(TecPojo tec) throws SQLException {
		Connection conn = SQLUtil.getConnection();
		String sql = "update tec set name = ?,age = ? where id = 11";
		PreparedStatement state = conn.prepareStatement(sql);
		state.setString(1, tec.getName());
		state.setInt(2, tec.getAge());
		int result = state.executeUpdate();
		if(result>0){
			System.out.println("Yes");
		}
		return result;
	}

	public List<TecPojo> query(TecPojo tec) throws SQLException {
		Connection conn = SQLUtil.getConnection();
		String sql = "select * from tec";
		PreparedStatement state = conn.prepareStatement(sql);
		ResultSet resultset = state.executeQuery();
		ArrayList list = new ArrayList();
		TecPojo tp = null;
		while(resultset.next()){
			tp = new TecPojo();
			tp.setId(resultset.getInt("id"));
			tp.setName(resultset.getString("name"));
			tp.setAge(resultset.getInt("age"));
			tp.setGender(resultset.getString("gender"));
			tp.setJob(resultset.getString("job"));
			tp.setCreateDate(resultset.getString("createDate"));
			list.add(tp);
		}
		for(int i = 0;i<list.size();i++){
			System.out.println(list.get(i));
		}
		return list;
	}

	public int delete(int id) throws SQLException {
		Connection conn = SQLUtil.getConnection();
		String sql = "delete from tec where id = "+id;
		PreparedStatement state = conn.prepareStatement(sql);
		int result = state.executeUpdate();
		if(result>0){
			System.out.println("Yes");
		}
		return result;
	}
}

Copy after login

TecPojo

package pojo;

public class TecPojo {
	private int id;
	private String name;
	private int age;
	private String gender;
	private String job;
	private String createDate;

	public TecPojo(String name, int age, String gender, String job, String createDate) {
		super();
		this.name = name;
		this.age = age;
		this.gender = gender;
		this.job = job;
		this.createDate = createDate;
	}
	
	public int getId() {
		return id;
	}

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

	public String toString() {
		return id+" "+name+" "+age+" "+gender+" "+job+" "+createDate;
	}

	public TecPojo() {
		super();
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

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

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public String getCreateDate() {
		return createDate;
	}

	public void setCreateDate(String createDate) {
		this.createDate = createDate;
	}

}

Copy after login

SQLUtil

package util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class SQLUtil {
	private static String url;
	private static String user;
	private static String password;
	private static String driver;
	static{
		try {
			Properties pro = new Properties();
			InputStream ins = SQLUtil.class.getResourceAsStream("/sqlconfig.properties");
			pro.load(ins);
			url = pro.getProperty("url");
			user = pro.getProperty("user");
			password = pro.getProperty("password");
			driver = pro.getProperty("driver");
			Class.forName(driver);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection(){
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url,user,password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
}

Copy after login

Test

package util;

import java.sql.SQLException;

import dao.impl.TecDaoImpl;
import pojo.TecPojo;

public class Test {
	public static void main(String[] args) {
		SQLUtil util = new SQLUtil();
		TecPojo tp = new TecPojo("bb",22,"man","teacher","2016-4-6");
		TecDaoImpl tdi = new TecDaoImpl();
		try {
//			tdi.insert(tp);
//			tdi.update(tp);
			tdi.query(tp);
//			tdi.delete(12);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

Copy after login

此时发现，TecDaoImpl的方法有多次重复，于是对重复的方法再次进行封装

SQLTemplete

package util;

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

public class SQLTemplete {
	public static int update(String sql,Object...object)throws SQLException{
		Connection conn = SQLUtil.getConnection();
		PreparedStatement state = conn.prepareStatement(sql);
		for(int i=0;i<object.length;i++){
			state.setObject(i+1, object[i]);
		}
		int result = state.executeUpdate();
		return result;
	}
	public static ResultSet query(String sql,Object...object)throws SQLException{
		Connection conn = SQLUtil.getConnection();
		PreparedStatement state = conn.prepareStatement(sql);
		for(int i=0;i<object.length;i++){
			state.setObject(i+1, object[i]);
		}
		ResultSet resultset = state.executeQuery();
		return resultset;
	}
}

Copy after login

TecDaoImpl

package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import dao.TecDao;
import pojo.TecPojo;
import util.SQLTemplete;
import util.SQLUtil;

public class TecDaoImpl implements TecDao{
	

	public int insert(TecPojo tec) throws SQLException {
		String sql = "insert into tec (name,age,gender,job,createDate)values(?,?,?,?,?)";
		int result = SQLTemplete.update(sql, tec.getName(),tec.getAge(),tec.getGender(),tec.getJob(),tec.getCreateDate());
		if(result>0){
			System.out.println("Yes");
		}
		return result;
	}


	public int update(TecPojo tec) throws SQLException {
		String sql = "update tec set name = ?,age = ? where id = 11";
		int result = SQLTemplete.update(sql, tec.getName(),tec.getAge());
		if(result>0){
			System.out.println("Yes");
		}
		return result;
	}

	public List<TecPojo> query(TecPojo tec) throws SQLException {
		String sql = "select * from tec";
		ResultSet resultset = SQLTemplete.query(sql);
		ArrayList<TecPojo> list = new ArrayList<TecPojo>();
		TecPojo tp = null;
		while(resultset.next()){
			tp = new TecPojo();
			tp.setId(resultset.getInt("id"));
			tp.setName(resultset.getString("name"));
			tp.setAge(resultset.getInt("age"));
			tp.setGender(resultset.getString("gender"));
			tp.setJob(resultset.getString("job"));
			tp.setCreateDate(resultset.getString("createDate"));
			list.add(tp);
		}
		for(int i = 0;i<list.size();i++){
			System.out.println(list.get(i));
		}
		return list;
	}

	public int delete(int id) throws SQLException {
		String sql = "delete from tec where id = "+id;
		int result = SQLTemplete.update(sql);
		if(result>0){
			System.out.println("Yes");
		}
		return result;
	}
}

Copy after login

这样实现了更彻底的封装，TecDaoImpl类里没有直接和数据库联系，用SQLTemplete类实现对数据库的操作，从而达到业务逻辑和数据访问之间的分离。