Java-Jdbc基础

JDBC

概念

JDBC(Java DataBase Connectivity（Java数据库的连接）):

是一种用于执行SQL语句(DML,DDL,DQL)的Java API，可以为多种关系数据库（oracle,mysql,SQL server）提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序.

不管使用哪一种数据库，都需要去导入这个数据库给我们提供的jdbc实现的jar包

访问数据库的两种形式

1.JDBC最原生的方式去访问

2.使用第三方框架来进行访问(底层依然是JDBC)

JDBC连接数据库

导入相应的jar包

​ jdbc连接数据库步骤：贾琏欲执事

​ 1.加载驱动

​ 2.建立连接

​ 3.获取语句

​ 4.执行语句

​ 5.关闭连接，释放资源

加载驱动三种方式

这里介绍最常用的一种：

try {
			//第一步：通过反射加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//第二步：建立连接
			Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbcdemo","root","594395fzk");
			//第三步：获取语句对象,建表,书写sql语句
			Statement statement = conn.createStatement();
			String sql = "create table student(id bigint(20),name varchar(255),age int(10))";
			//第四步：执行sql语句
			//boolean b = statement.execute(sql);
			String ins = "insert into student (id,name,age) value ('02','电棍','2')";
			statement.executeUpdate(ins);
			//第五步：释放资源，关闭数据库连接
			statement.close();
			conn.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

至于其他两种：😝😝😝😝😝百度一下，你就知道

使用JDBC实现增删改查

package com.ifueen.classtest.jdbcdemo;

import static org.junit.Assert.*;

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

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class JdbcConnect {
	Connection conn;
	Statement statement;
	@Before
	public void before() {
		try {
			//加载启动
			Class.forName("com.mysql.jdbc.Driver");
			//连接数据库
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbcdemo","root","594395fzk");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * @throws Exception
	 * 添加数据
	 */
	@Test
	public void insert() throws Exception {
		String ins = "insert into student (id,name,age) values (5,'窦唯',20)";
		statement = conn.createStatement();
		statement.execute(ins);
	}
	/**
	 * @throws Exception
	 * 删除数据
	 */
	@Test
	public void delete() throws Exception {
		String del = "delete from student where id=5";
		statement = conn.createStatement();
		statement.execute(del);
	}
	/**
	 * @throws Exception
	 * 更新数据
	 */
	@Test
	public void update() throws Exception {
		String update = "update student set name='小小山泥若' where id=1";
		statement = conn.createStatement();
		statement.executeUpdate(update);
	}
	/**
	 * @throws Exception
	 * 查询数据
	 */
	@Test
	public void select() throws Exception {
		String select = "select * from student";
		statement = conn.createStatement();
		ResultSet resultSet = statement.executeQuery(select);
		while(resultSet.next()){
			System.out.println(resultSet.getString("name"));
		}
	}
	/**
	 * @throws SQLException
	 * 关闭连接，释放资源
	 */
	@After
	public void after() throws SQLException{
		statement.close();
		conn.close();
	}
	
}

封装JDBC工具类

为了更方便，让代码使用起来更高效，可以将连接数据库的操作封装成一个工具类，以后在每次要进行数据库连接的时候直接使用工具类即可

这里先将数据库的驱动连接，url，账户密码等放在了资源文件夹的properties文件中

资源文件db.properties:

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/jdbcwork
username=root
password=594395fzk

封装的工具类:

package com.ifueen.classtest.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
		
public class JDBCUtil {
	//创建单例模式
	private static JDBCUtil instance;
	private JDBCUtil() {}
	public static JDBCUtil getinstance() {
		return instance;
	}
	private static Properties prop = new Properties();
	//静态代码块，存放要优先加载的功能语句
	static{
		try {
			//通过字节码来获取流
			prop.load(JDBCUtil.class.getResourceAsStream("/db.properties"));
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		instance = new JDBCUtil();
		try {
			//加载驱动
			Class.forName(prop.getProperty("driverClassName"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * @return
	 * 连接数据库的方法
	 */
	public  Connection getconn() {
		 Connection conn = null;
		try {
			//连接数据库
			conn = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 
	 * 关闭连接，释放资源
	 */
	public  void getclose(ResultSet rs, Statement st, Connection conn) {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			try {
				if (st != null)
					st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {

				try {
					if (conn != null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	public  void getclose(Statement st, Connection conn) {
			try {
				if(st!=null)
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				if(conn!=null)
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
	}
}

这下连接数据库的操作就很好地封装成了一个工具类，只需要在要进行操作的地方使用即可

测试类：

package com.ifueen.classtest.util;

import static org.junit.Assert.*;

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

import org.junit.Test;

public class UtilTest {

	@Test
	public void test() throws Exception {
		//调用封装好的加载驱动的方法
		Connection conn = JDBCUtil.getinstance().getconn();
		Statement st = conn.createStatement();
		String sql = "select * from student";
		ResultSet rs = st.executeQuery(sql);
		while (rs.next()) {
			System.out.println(rs.getString("name"));
		}
		//调用关闭驱动的方法
		JDBCUtil.getinstance().getclose(rs, st, conn);
	}

}

Dao层的实现

DAO : 主要是用来做数据库的操作
模块化,分工明确,解耦(责任分离)

刚才已经说到要分层，各层之间相互调用，现在数据层 调用 数据库，需要传什么内容?
比如 要添加一个用户，怎么添加?

界面层：收集数据； name，password，age….等等 需要传递很多东西;
业务层：界面层收集之后，传到业务层;

因为我们没有前端传过来的数据，现在只使用dao,domian层来进行crud操作

大体结构如下

首先肯定要在数据库中将表建好，然后写一个javabean类和这张表一一对应

domian层下面的javabean类：

package com.ifueen.homework.domian;

public class Student {
	private long id;
	private String username;
	private String password;
	private int age;
	private boolean sex;
	private String intro;
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public boolean isSex() {
		return sex;
	}
	public void setSex(boolean sex) {
		this.sex = sex;
	}
	public String getIntro() {
		return intro;
	}
	public void setIntro(String intro) {
		this.intro = intro;
	}
	public Student(long id, String username, String password, int age, boolean sex, String intro) {
		super();
		this.id = id;
		this.username = username;
		this.password = password;
		this.age = age;
		this.sex = sex;
		this.intro = intro;
	}
	public Student(Long id,String username) {
		super();
		this.username = username;
		this.id = id;
	}
	public Student() {
		super();
	}
	
}

Dao类:

package com.ifueen.homework.dao;

import com.ifueen.homework.domian.Student;

public interface StudentDao {
	//增加数据
	void add(Student s);
	//删除数据
	void delete(long id);
	//修改数据
	void update(Student s);
	//查找数据
	Student select(Student s);
	//查找数据2
	void select(Long id);
}

dao的实现类

package com.ifueen.homework.dao.impl;

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

import com.ifueen.classtest.util.JDBCUtil;
import com.ifueen.homework.dao.StudentDao;
import com.ifueen.homework.domian.Student;
import com.sun.javafx.binding.Logging;

public class StudentDaoImpl implements StudentDao{

	/** 
	 * 新增数据
	 */
	@Override
	public void add(Student s) {
		// TODO Auto-generated method stub
		try {
			//加载驱动，连接数据库
			Connection conn = JDBCUtil.getinstance().getconn();
			//得到Statement对象
			Statement st = conn.createStatement();
			//写sql语句
			String sql = "insert into student(id,username,password,age,sex,intro) "
					+ "values ('"+s.getId()+"','"+s.getUsername()+"','"+s.getPassword()+"','"+s.getAge()+"','"+s.isSex()+"','"+s.getIntro()+"')";
			//执行sql语句
			st.execute(sql);
			//关闭连接，释放资源
			JDBCUtil.getinstance().getclose(st, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 删除数据
	 */
	@Override
	public void delete(long id) {
		// TODO Auto-generated method stub
		try {
			Connection conn = JDBCUtil.getinstance().getconn();
			Statement st = conn.createStatement();
			String sql = "delete from student where id="+id;
			st.execute(sql);
			JDBCUtil.getinstance().getclose(st, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/** 
	 * 修改数据
	 */
	@Override
	public void update(Student s) {
		// TODO Auto-generated method stub
		try {
			Connection conn = JDBCUtil.getinstance().getconn();
			Statement st = conn.createStatement();
			String sql = "update student set username='"+s.getUsername()+"' where id="+s.getId();
			st.executeUpdate(sql);
			JDBCUtil.getinstance().getclose(st, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 查询数据1
	 */
	@Override
	public Student select(Student s) {
		Student student = null;
		// TODO Auto-generated method stub
		try {
			Connection conn = JDBCUtil.getinstance().getconn();
			Statement st = conn.createStatement();
			String sql = "select * from student";
			ResultSet set = st.executeQuery(sql);
			student = new Student();
			while (set.next()) {
				student.setId(set.getLong("id"));
				student.setUsername(set.getString("username"));
				student.setPassword(set.getString("password"));
				student.setAge(set.getInt("age"));
				student.setSex(set.getBoolean("sex"));
				student.setIntro(set.getString("intro"));
			}
			JDBCUtil.getinstance().getclose(st, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return student;
	}
	
	/**
	 * 查询数据2
	 * 全部循环打印显示出来
	 */
	@Override
	public void select(Long id) {
		Student student = null;
		// TODO Auto-generated method stub
		try {
			Connection conn = JDBCUtil.getinstance().getconn();
			Statement st = conn.createStatement();
			String sql = "select * from student where id="+id;
			ResultSet set = st.executeQuery(sql);
			student = new Student();
			while (set.next()) {
				System.out.print(set.getLong("id")+"  ");
				System.out.print(set.getString("username")+"  ");
				System.out.print(set.getString("password")+"  ");
				System.out.print(set.getInt("age")+"  ");
				System.out.print(set.getBoolean("sex")+"  ");
				System.out.print(set.getString("intro")+"  ");
			}
			JDBCUtil.getinstance().getclose(st, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

最后测试类：

package com.ifueen.homework.test;

import static org.junit.Assert.*;

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

import org.junit.Before;
import org.junit.Test;

import com.ifueen.classtest.util.JDBCUtil;
import com.ifueen.homework.dao.StudentDao;
import com.ifueen.homework.dao.impl.StudentDaoImpl;
import com.ifueen.homework.domian.Student;

public class StudentTest {
	StudentDao std = new StudentDaoImpl();

	/**
	 * 添加数据
	 */
	@Test
	public void add() {
		Student student = new Student(1,"山泥若","123456",25,true,"我顶不住了");
		std.add(student);
	}
	/**
	 * 删除数据
	 */
	@Test
	public void delete() {
		std.delete(1L);
	}
	/**
	 * 修改数据
	 */
	@Test
	public void update() {
		Student student = new Student(2L,"落日飞车");
		std.update(student);
	}
	/**
	 * 查询数据
	 */
	@Test
	public void select() {
		Student student = new Student(1,"山泥若","123456",25,true,"我顶不住了");
		//拿到查询的结果
		Student select = std.select(student);
		System.out.println(select.getUsername());
		System.out.println(select.getIntro());
	}
	/**
	 * 查询数据2
	 */
	@Test
	public void selects() {
		std.select(1L);
	}

}