JDBC入门

JDBC入门

一、JDBC介绍

(一)概念

1.Java Data Base Connectivity（Java数据库的连接）

2.java提供的一个标准由各大数据库厂商实现数据的持久化操作

3.把数据保存到数据库，JDBC是Java代码操作数据库的唯一技术，操作数据库只需要导入我们的jar包

(二)持久化

1.将内存中的数据存储在关系型数据库中

2.持久化数据的最好方式就是把数据保存到数据库

二、JDBC完成CRUD

贾琏欲执行

Connection con = null;
Statement sta = null;
ResultSet rs = null;

1.贾 【加】载驱动

Class.forName("com.mysql.jdbc.Driver");

2.琏 获取数据库【连】接

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库的名称", "用户名", "密码");

3.欲 获取【语】句对象 statement 对象

Statement sta = con.createStatement();

4.执 【执】行sql语句

（1）创建表

String sql = "CREATE TABLE `java创建表` ("
		+ "`id` int(2) NOT NULL AUTO_INCREMENT,"
		+ "`username` varchar(20) DEFAULT NULL,"
		+ "`password` varchar(20) DEFAULT NULL,"
		+ "PRIMARY KEY (`id`)"
		+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
sta.execute(sql);

（2）添加数据

String sql = "insert into java创建表(username,password) values ('小王','123456')";
sta.execute(sql);

（3）修改数据

String sql = "update java创建表 set username = '王王' where id = 1";
sta.execute(sql);

（4）删除数据

String sql = "delete from java创建表 where id = 2";
sta.execute(sql);

（5）查询一条数据

String sql = "select * from java创建表  where id = 1";
rs = sta.executeQuery(sql);
while (rs.next()) {
	String username = rs.getString("username");
	String password = rs.getString("password");
	System.out.println(username + "   " + password);
}

（6）查询全部数据

String sql = "select * from java创建表";
rs = sta.executeQuery(sql);
while (rs.next()) {
	String username = rs.getString("username");
	String password = rs.getString("password");
	System.out.println(username + "   " + password);
}

5.事 【释】放资源

sta.close();
con.close();

三、DAO层

（一）概念

界面层 — 业务层 – 数据层

（二）思路

1. 先创建一个domain包 ,在包里面创建相应的实体类

​ cn.itsource.domain

2. 在domain包的同一级,建一个dao包,这个包里面写接口:

​ cn.itsource.dao

​ 接口名称: IXxxDAO / IXxxDao

public interface IUserDAO {
	//添加数据方法 
	void add(User user);
	//修改数据方法
	void update(User user);
	//删除数据方法
	void delete(User user);
	//查找全部数据方法
	List<User> findAll();
}

3. 在dao这个包里面再建一个包: impl 包

   cn.itsource.dao.impl

​ 包类的名称：XxxDAOImpl/XxxDaoImpl

public class UserDAOImpl implements IUserDAO{

	@Override
	public void add(User user) {
		Connection con = null;
		Statement sta = null;
		try {
			con = JDBCUtilPro.getCon();
			sta = con.createStatement();
			String sql = "insert into java创建表  (username,password) values ('"+user.getUserName()+"','"+ user.getPassword()+"')";
			sta.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtilPro.closeIO(sta, con, null);
		}
	}

	@Override
	public void update(User user) {
		Connection con = null;
		Statement sta = null;
		try {
			con = JDBCUtilPro.getCon();
			sta = con.createStatement();
			String sql = "update java创建表  set username = '"+user.getUserName()+"' where id = "+user.getId()+" ";
			sta.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtilPro.closeIO(sta, con, null);
		}
	}

	@Override
	public void delete(User user) {
		Connection con = null;
		Statement sta = null;
		try {
			con = JDBCUtilPro.getCon();
			sta = con.createStatement();
			String sql = "delete from java创建表  where id = "+user.getId()+" ";
			sta.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtilPro.closeIO(sta, con, null);
		}
	}		

	@Override
	public List<User> findAll() {
		Connection con = null;
		Statement sta = null;
		ResultSet rs = null;
		ArrayList<User> list = new ArrayList<User>();
		try {
			con = JDBCUtilPro.getCon();
			sta = con.createStatement();
			String sql = "select * from java创建表";
			rs = sta.executeQuery(sql);
			while (rs.next()) {
				System.out.println(rs.getString("username")+"	"+rs.getString("password"));
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUserName(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtilPro.closeIO(sta, con, null);
		}

		return list;
	}

}

4. 完成dao的crud

public class UserDAOImplTest {
	UserDAOImpl udi = new UserDAOImpl();
	User user = new User();
	
	//添加
	@Test
	public void testAdd(){
		user.setUserName("白白");
		user.setPassword("135790");
		udi.add(user);
	}
	
	//修改
	@Test
	public void testUpdate(){
		user.setUserName("猪皮");
		user.setId(5);
		udi.update(user);
	}
	
	//删除
	@Test
	public void testDelete(){
		user.setId(4);
		udi.delete(user);
	}
	
	//查询所有
	@Test
	public void testFindAll(){
		udi.findAll();
	}
}

四、重构

抽取封装

public class JDBCUtil {
	//拿到连接对象
	public static Connection getCon(){
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysqlday10.17", "root", "979483");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}
}

放到资源文件封装

public class JDBCUtilPro {
	//拿到配置文件
	public static Properties p = new Properties();
	static{
		try {
			p.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));
			Class.forName(p.getProperty("className"));
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	// 拿到连接对象
	public static Connection getCon() {
		Connection con = null;
		try {
			con = DriverManager.getConnection(p.getProperty("url"), p.getProperty("user"), p.getProperty("password"));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
    }
}

关流

    //关流
	public static void closeIO(Statement sta,Connection con,ResultSet rs){
		try {
			if (sta != null) {
				sta.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (con != null) {
					con.close();	
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				try {
					if (rs != null) {
						rs.close();	
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}