工具类封装

工具类封装

JDBCOperation DBUtil DBTool

1编码 向数据库MySQL中插入数据乱码

    ?useUnicode = true &characterEncoding = utf8

&useSSL=false

2：PrepareStatement  预处理statement

prepareStatement statemennt区别？

Sql 不同

Statement要求SQL语句直接追加数据内容

prepareStatement要求处理的数据单独的分离书写，增加程序可读性

SQL注入问题

Select *from user where username = “张三” and password = ‘’ or “1”  = ”1”

prepareStatement  防止SQL的注入问题

3:SSL问题 安全链接 兼容性问题

Security socket layer 安全套接字协议层

4：工具类封装        

什么情况下会考虑封装？

重复代码

经常用

如何封装？

DBUtil.java

静态方法

封装呢些功能？

1：建立连接（加载驱动，建立连接）

2：释放

注意工具类方法的参数和返回值类型

1：update( String sql ,Object[] objects) INSERT

UPDATE DELETE

2：query(String sql ,Object[] objects) SELECT

JDBCDemo.java

/**
 * 
 */
package com.zhiyou.jdbcDemo;

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

/**
 * @author Administrator
 *
 */
public class JDBCDemo {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		insertData();
	}

	public static void insertData() {
		try {
//			反射 机制
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/school?characterEncoding=utf8&useSSL=false";
			String user = "root";
			String password = "123456";
			Connection coon = DriverManager.getConnection(url, user, password);
//			?相当于占位符
			String sql = "insert into employer (e_name,e_age,e_date,d_id) values(?,?,?,?)";
			PreparedStatement ps = coon.prepareStatement(sql);
//			设置占位符
			ps.setString(1, "弗雷尔卓德");
			ps.setInt(2, 134);
			ps.setString(3, "2078-02-25");
			ps.setInt(4, 2);
//			执行
			int rs = ps.executeUpdate();
			if (rs > 0) {
				System.out.println("插入成功！");
			}
			ps.close();
			coon.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}

 DBUtil.java

/**
 * 
 */
package com.zhiyou.tools;

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

/**
 * @author Administrator
 *
 */
public class DBUtil {

	/**
	 * @param args
	 */
//	驱动
	private final static String DRIVER_CLASS = "com.mysql.jdbc.Driver";

//	账号密码URL
	private final static String URL = "jdbc:mysql://localhost:3306/school?characterEncoding=utf8&useSSL=false";
	private final static String USER = "root";
	private final static String PASSWORD = "123456";

	private static Connection connection = null;
	private static PreparedStatement ps = null;
	private static ResultSet rs = null;

//	获得驱动
	static {

		try {
			Class.forName(DRIVER_CLASS);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

//	获得一个链接
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		connection = DriverManager.getConnection(URL, USER, PASSWORD);

		return connection;
	}

//	释放的方法
	public static void close() throws SQLException {
		if (rs != null) {
			rs.close();
		}
		if (ps != null) {
			ps.close();
		}
		if (connection != null) {
			connection.close();
		}
	}

//	CRUD
	public static int update(String sql, Object... obj) throws SQLException, ClassNotFoundException {
//		1：获取连接
		connection = getConnection();
//		2：准备SQL语句
		ps = connection.prepareStatement(sql);
//		填充？
		for (int i = 0; i < obj.length; i++) {
			ps.setObject(i + 1, obj[i]);
		}
//		执行SQL并返回记录数
		return ps.executeUpdate();
	}

	public static ResultSet query(String sql, Object... objects) throws SQLException, ClassNotFoundException {
//		获得连接
		connection = getConnection();
//		准备SQL语句
		ps = connection.prepareStatement(sql);
//		填充？
		for (int i = 0; i < objects.length; i++) {
			ps.setObject(i + 1, objects[i]);
		}
		rs = ps.executeQuery();
		return rs;
	}
}

 EmployerObj.java

/**
 * 
 */
package com.zhiyou.model;

/**
 * @author Administrator
 *
 */
public class EmployerObj {
	private int e_id;
	private String e_name;
	private int e_age;

	public EmployerObj() {
		super();
		// TODO Auto-generated constructor stub
	}

	public EmployerObj(int e_id, String e_name, int e_age) {
		super();
		this.e_id = e_id;
		this.e_name = e_name;
		this.e_age = e_age;
	}

	public int getE_id() {
		return e_id;
	}

	public void setE_id(int e_id) {
		this.e_id = e_id;
	}

	public String getE_name() {
		return e_name;
	}

	public void setE_name(String e_name) {
		this.e_name = e_name;
	}

	public int getE_age() {
		return e_age;
	}

	public void setE_age(int e_age) {
		this.e_age = e_age;
	}

	@Override
	public String toString() {
		return "EmployerObj [e_id=" + e_id + ", e_name=" + e_name + ", e_age=" + e_age + "]";
	}

}

 IEmployerDao.java

/**
 * 
 */
package com.zhiyou.dao;

import java.util.List;

import com.zhiyou.model.EmployerObj;

/**
 * @author Administrator
 *
 */
public interface IEmployerDao {
	
//	获得所有员工信息
	List<EmployerObj> getAll ();
}

 EmployerDaoImpl.java

package com.zhiyou.impl;

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

import com.zhiyou.model.EmployerObj;
import com.zhiyou.tools.DBUtil;

import com.zhiyou.dao.IEmployerDao;

public class EmployerDaoImpl implements IEmployerDao{

	@Override
	public List<EmployerObj> getAll() {
		// TODO Auto-generated method stub
//		操作表
		String sql = "select *from employer";
		Object[] objects = {};

		List<EmployerObj> list = new ArrayList<>();
		try {
			ResultSet rs = DBUtil.query(sql, objects);
			while (rs.next()) {
				int id = rs.getInt("e_id");
				String name = rs.getString("e_name");
				int age = rs.getInt("e_age");
				EmployerObj obj = new  EmployerObj(id, name, age);
				list.add(obj);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

}

 Test.java

/**
 * 
 */
package com.zhiyou.jdbcDemo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.zhiyou.impl.EmployerDaoImpl;
import com.zhiyou.model.EmployerObj;
import com.zhiyou.tools.DBUtil;

/**
 * @author Administrator
 *
 */
public class Test {

	/**
	 * @param args
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
		DBUtil.getConnection();

		String sql = "update employer set e_name=? where e_age = ?";
		Object[] obj = { "维克托", 123 };
		DBUtil.update(sql, obj);

		String sql1 = "select * from employer where e_age<?";
		Object[] objects = { 100 };
		ResultSet rs = DBUtil.query(sql1, objects);
		while (rs.next()) {
			System.out.println(rs.getInt("e_id") +"\t"
					+ rs.getString("e_name") +"\t"
					+ rs.getInt("e_age") +"\t"
					+ rs.getString("e_date")+"\t"
					+ rs.getInt("d_id"));
		}

		DBUtil.close();
		
		EmployerDaoImpl employerDaoImpl = new EmployerDaoImpl();
		List< EmployerObj> list = employerDaoImpl.getAll();
		System.out.println(list);
	}

}