再敲一遍------JDBC基本操作与Utils封装

一.JDBC的简介

1.JDBC是什么?

JDBC就是java连接数据库

2.基本步骤

分步走:

1> 导jar包

1.1> 在项目下创建文件夹lib,将上图jar用CV大法丢进去

1.2> 代码先自行过滤,右击鼠标,选择BuildPath,添加关联

1.3>效果图

2>注册驱动(可省略)

  //括号里的为关联的jar包里面的字节码文件路径,可见下图
  //Class.forName("com.mysql.jdbc.Driver");书写下来,按错号自行捕获
	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

下图:

3>获取数据库连接(mysql)

  //括号里的三个参数如下图

  Connection con = DriverManager.getConnection("jdbc:mysql://
                 localhost:3306/db5", "root", "111111");
                 
  注:此步需要捕获异常,一般选择捕获,也可以抛异常

url:数据库的本机地址,user为数据库用户名,password为密码

4>写sql语句

  //此例展示的是创建数据库
  String sql = "CREATE TABLE users("
					+ "id INT PRIMARY KEY AUTO_INCREMENT" + 
					"NAME VARCHAR(20)," + 
					"age INT," + 
					"address VARCHAR(30)" + 
					")";
 注:按照sql语法格式严格书写,否则会报sql语句相关错误

5>预编译

  PreparedStatement statement = con.prepareStatement(sql);

6>执行更新/查询操作

   //此为执行更新操作
  int i = statement.executeUpdate();
  
 注:executeQuery()为数据查询,执行查询
    executeUpdate()其他操作,执行更新

7>输出结果

System.out.println("总共影响了"+i+"行");

注:上步操作已经实现了

8>释放资源

  if(statement != null) {
				con.close();
  }
  if(con != null) {
				statement.close();
  }
注:后出现的先释放

完整步骤:

public class Demo {
	public static void main(String[] args) {

		try {
			//注册驱动
			Class.forName("com.mysql.jdbc.Driver");
			//获取数据库连接
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db5", "root", "111111");
			
			//System.out.println(con);
			
			//写sql语句
			String sql = "CREATE TABLE user3("
					+ "id INT PRIMARY KEY AUTO_INCREMENT," + 
					"NAME VARCHAR(20)," + 
					"age INT," + 
					"address VARCHAR(30)" + 
					")";
			//预编译,获取执行对象
			PreparedStatement statement = con.prepareStatement(sql);
			//执行更新操作
			int i = statement.executeUpdate();
			//输出结果
			System.out.println("总共影响了"+i+"行");
			//释放资源
			if(con != null) {
				con.close();
			}
			if(statement != null) {
				statement.close();
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

二.封装工具类

1.基本步骤:

<1>写jdbc.properties文件

 <1.1>在src根目录下,新建jdbc.properties文件
 <2.2>文件书写

注:根据一般修改端口号,库名,密码,用户名就可; 密不可分,不可有空格

<2>书写工具类

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

public class JdbcUtils {
	//构造方法私有化
	private JdbcUtils() {};
	//成员变量私有化
	private static String url;
	private static String user;
	private static String password;
	private static String driver;
	
	//static代码块类加载
	static {
		try {
			Properties pre = new Properties();
			InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
			pre.load(in);
			url = pre.getProperty("url");
			user = pre.getProperty("user");
			password = pre.getProperty("password");
			driver = pre.getProperty("driver");
			Class.forName(driver);
		} catch (IOException | ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	//获取数据库连接
	public static Connection getConnection() {
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, user, password);
			return con;
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return null;	
	}
	//释放资源
	public static void close(Connection con , PreparedStatement statement , ResultSet set) {
		if(set != null) {
			try {
				set.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void close(Connection con , PreparedStatement statement) {
		close(con,statement,null);
	}	
}

 注:实质就是为了把注册驱动,数据库连接,释放资源功能封装起来
   通过读取properties文件来执行,可方便更替

<3>测试(添加)

//添加操作(删除,修改操作基本一致)
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.qiao.Utils.JdbcUtils;

public class Demo2 {
	private static Connection con = null;
	private static PreparedStatement statement = null;
	public static void main(String[] args) {
		try {
			//获取数据库连接
			con = JdbcUtils.getConnection();
			//写sql语句
			String sql = "insert into user2 values (?,?,?,?)";
			//预编译
			statement = con.prepareStatement(sql);
			//参数赋值
			statement.setInt(1, 2);
			statement.setString(2, "小李子");
			statement.setInt(3, 18);
			statement.setString(4, "北京天安门");
			//执行sql语句
			int i = statement.executeUpdate();
			//输出结果
			System.out.println("影响了"+i+"行");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JdbcUtils.close(con, statement);
		}
	}
}

<4>测试(查询)

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

import com.qiao.Utils.JdbcUtils;

public class Demo3 {
	private static Connection con = null;
	private static PreparedStatement statement = null;
	private static ResultSet set = null;
	public static void main(String[] args) {
		selectDateByid(2);
	}


	private static void selectDateByid(int i) {
		try {
			// 获取数据库连接
			con = JdbcUtils.getConnection();
			// 写sql语句
			String sql = "select * from user2 where id = ?";
			// 预编译
			statement = con.prepareStatement(sql);
			//参数赋值
			statement.setInt(1, i);
			// 输出结果
			set = statement.executeQuery();
			//遍历
			while(set.next()) {
				int id = set.getInt(1);
				String name = set.getString(2);
				int age = set.getInt(3);
				String address = set.getString(4);
				//输出
				System.out.println(id+"\t"+name+"\t"+age+"\t"+address);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {	
			JdbcUtils.close(con, statement,set);
		}
	}
}
注:也可以通过实体类封装,进行存储,查询