Java进阶之JDBC

Java进阶之JDBC

一、JDBC初识

JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成

JDBC常用接口：
Connection接口
Statement接口
PreparedStatement接口
ResultSet接口
CallableStatement接口
DriverManager类

二、JDBC驱动安装

下载JDBC驱动包
mysql官方网站进行下载

配置JDBC驱动
    方式一： 
选中工程右击->Build Path->Add External Archival->选择mysql驱动包->Ok
方式二：
把mysql驱动包直接粘贴到工程目录下->选中jar包右击->Build Path->Add to BuildPath

驱动加载获取连接步骤：
1.加载驱动类
  Class.forName("com.mysql.jdbc.Driver");
2.通过地址和用户名密码获取连接
 DriverManager.getConnection(jdbc:mysql://地址:端口/l","root","admin");

三、JDBC增删查改操作

jdbc增删改操作
1、获取连接
2、获取Statement对象
    Connection.createStatement()
3、整理插入的sql语句字符串
  String sql="sql语句"
4、发送并执行sql语句
 Statement.executeUpdate(sql语句);
5、关闭连接

jdbc查找操作
1、获取连接
2、获取Statement对象
    Connection.createStatement()
3、整理查询的sql语句字符串
  String sql="sql语句"
4、发送并执行sql语句
 ResultSet rs = Statement.executeQuery(sql语句);
5、处理结果集
6、关闭连接

ResultSet接口
对于数据库查询操作，ResultSet主要用于接收查询出来的结果集
常用方法
next、getInt、getDate、getString

四、预编译对象

预编译对象
PreparedStatement 接口继承了Statement 
可以高效的重复执行sql语句
PreparedStatement 实例包含已编译的 SQL 语句


编写语法
PreparedStatement pstmt = con.prepareStatement("select * from table_name where name = ?");

五、代码演示：

package MySqlConnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class ConnectionTest {
	
	public ArrayList<Teacher> list = new ArrayList<Teacher>();

	public static void main(String[] args) {
		ConnectionTest test = new ConnectionTest();
		// test.preInsert();
		test.insert1(76, "ZZZZ", "man", 23, "Studeng", "2017-02-21");
	}

	public void insert() {
		try {
			// 1、获取连接
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhou", "root", "zhou");
			// 2.创建StateMent对象
			Statement state = connection.createStatement();
			// 3.整理一条sql数据
			String sql = "INSERT into teacher VALUES (10,'zhouzhou','man',21,'student','2017-02-24');";
			// 4.强这条数据插入到数据库中
			state.execute(sql);
			// 关闭连接
			state.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void delete() {

		try {
			// 1.获取连接
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhou", "root", "zhou");
			// 2.创建StateMent对象
			Statement state = connection.createStatement();
			// 3.整理删除sql语句
			String sql = "delete from teacher where id=10";
			// 4.发送并执行sql语句
			state.execute(sql);
			// 5.关闭资源
			state.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void updata() {
		try {
			// 1.获取连接
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhou", "root", "zhou");
			// 2.创建Statement对象
			Statement state = connection.createStatement();
			// 3.正路sql语句
			String sql = "update teacher set name='KeChuang' where id=6;";
			// 4.发送并执行sql语句
			state.execute(sql);
			// 5.关闭资源
			state.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void query() {
		try {
			// 1.获取连接
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhou", "root", "zhou");
			// 2.创建Statement对象
			Statement state = connection.createStatement();
			// 3.整理sql语句
			String sql = "select * from teacher;";
			// 4.发送并执行sql语句
			ResultSet rs = state.executeQuery(sql);
			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String gender = rs.getString(3);
				int age = rs.getInt(4);
				String job = rs.getString(5);
				String date = rs.getString(6);
				System.out.println("id: " + id + "   " + "name: " + name + "   gender: " + gender + "   age: " + age
						+ "   job: " + job + "   date: " + date + ": ");
			}
			// 5.关闭资源
			state.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void preInsert() {

		try {
			// 1.获取连接
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhou", "root", "zhou");
			// 2.创建prepareStatement对象
			PreparedStatement prement = connection
					.prepareStatement("insert into teacher values(278,'ZuoJiang','man',22,'Student','2017-01-11');");
			// 3.发送并执行sql语句
			prement.execute();
			// 4.关闭资源
			prement.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void preInsert(int id, String name, String gender, int age, String job, String date) {

		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2.获取和数据库的简介
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhou", "root", "zhou");
			// 3.获取预编译对象
			PreparedStatement ment = connection
					.prepareStatement("insert into teacher (id,name,gender,age,job,createDate) VALUES (?,?,?,?,?,?);");
			ment.setInt(1, id);
			ment.setString(2, name);
			ment.setString(3, gender);
			ment.setInt(4, age);
			ment.setString(5, job);
			ment.setString(6, date);
			ment.execute();
			// 4.关闭资源
			ment.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void insert1(int id, String name, String gender, int age, String job, String date) {

		try {
			// 1.获取连接
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhou", "root", "zhou");
			// 2.创建Statement对象
			Statement state = connection.createStatement();
			// 3.整理一条sql语句
			String sql = "insert into teacher (id,name,gender,age,job,createDate) values (" + id + ",'" + name + "','"
					+ gender + "','" + age + "','" + job + "','" + date + "'" + ");";
			// 4.发送并执行sql语句
			// System.out.println(sql);
			state.execute(sql);
			// 5.关闭资源
			state.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void queryAll() {

		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2.获取连接
			Connection connection = DriverManager.getConnection("jdbc:mydql://localhost:3306/zhou", "root", "zhou");
			Statement state = connection.createStatement();
			// 3.整理sql语句
			String sql = "select * from teacher";
			// 4.发送并执行sql语句
			ResultSet set = state.executeQuery(sql);
			while (set.next()) {
				int id = set.getInt(1);
				String name = set.getString(2);
				String gender = set.getString(3);
				int age = set.getInt(4);
				String job = set.getString(5);
				String date = set.getString(6);
				Teacher t = new Teacher(id, name, gender, age, job, date);
				list.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

package MySqlConnection;

public class Teacher {
	
	private int id;
	private String name;
	private String gender;
	private int age;
	private String job;
	private String date;
	
	public Teacher(int id, String name, String gender, int age, String job, String date) {
		super();
		this.id = id;
		this.name = name;
		this.gender = gender;
		this.age = age;
		this.job = job;
		this.date = date;
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public String getDate() {
		return date;
	}
	public void setDate(String date) {
		this.date = date;
	}

}

六、总结

上面的代码首先的安装好Mysql数据库以及加载相应的驱动包；

以前刚接触JDBC的时候觉得很难，现在把思路整理清楚了，其实也很简单；

你可能觉得加载驱动那一段长长的代码可能会有点怕，其实不用紧张，多敲几遍就好了，看上面，我就敲了好几遍；

写程序真的思路很重要啊，不过思路是在你理解了相关的知识之后的；基础没打好，学习到后面可能就会吃力；所以，Java基础一定要学好；可能你觉得你的Java基础学得不错了，其实那可能还只是皮毛；

共勉！