JDBC的记录

一、JDBC（Java Data Base Connectivity,java数据库连接）

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

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

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

（4）驱动加载获取连接步骤：
1.加载驱动类   

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

2.通过地址和用户名密码获取连接 

DriverManager.getConnection(jdbc:mysql://地址:端口/l","root","admin");

（5）jdbc增删改操作
1、获取连接
2、获取Statement对象     

 Connection.createStatement()

3、整理插入的sql语句字符串  

 String sql="sql语句"

4、发送并执行sql语句

Statement.executeUpdate(sql语句);

5、关闭连接
（6）jdbc查找操作
1、获取连接
2、获取Statement对象  

   Connection.createStatement()

3、整理查询的sql语句字符串

  String sql="sql语句"

4、发送并执行sql语句

 ResultSet rs = Statement.executeQuery(sql语句);

5、处理结果集
6、关闭连接

（7）ResultSet接口
1、对于数据库查询操作，ResultSet主要用于接收查询出来的结果集

        2、常用方法
next、getInt、getDate、getString

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

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

实现jdbc的Statement对数据的增删查改操作

package com.test.jdbc;

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

import com.mysql.jdbc.ResultSet;

public class JDBCTest {
	public static void main(String[] args) {
		JDBCTest jc = new JDBCTest();
		// String sql =
		// "insert into teacherzhang(id,name,gender,age,job,createdate) values(110,'lhh','F',21,'teacher','2017-2-4 22:11:11')";
		// String sql = "delete from teacherzhang where id=110";
		// String sql = "update teacherzhang set name='lhh' where id=1";
		// jc.insert(sql);
		// jc.delete(sql);
		// jc.update(sql);

		// Teacher tea = new Teacher(3, 12, "hg", "n", "it",
		// "2017-2-4 22:11:11");
		// jc.insert(tea);
		// jc.delete(tea);
		// jc.update(tea, 99);
		ArrayList<Teacher> list = jc.queryAll();
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
		}
	}

	/**
	 * 获取Connection连接对象
	 * 
	 * @return
	 */
	public Connection getConn() {
		Connection conn = null;
		try {
			// 加载
			Class.forName("com.mysql.jdbc.Driver");
			// 获取连接对象
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/lhz", "root", "lhz1314");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 插入操作
	 * 
	 * @param tea
	 */
	public void insert(Teacher tea) {
		Connection con = getConn();
		try {
			// 获取Statement对象
			Statement state = con.createStatement();
			// 插入语句
			String sql = "insert into teacherzhang(id,name,gender,age,job,createdate) values("
					+ tea.getId()
					+ ",'"
					+ tea.getName()
					+ "','"
					+ tea.getGender()
					+ "',"
					+ tea.getAge()
					+ ",'"
					+ tea.getJob() + "','" + tea.getCreatedate() + "')";
			state.execute(sql);
			state.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 删除操作
	 * 
	 * @param tea
	 */
	public void delete(Teacher tea) {
		Connection con = getConn();
		try {
			// 获取Statement对象
			Statement state = con.createStatement();
			// 删除语句
			String sql = "delete from teacherzhang where id=" + tea.getId()
					+ "";
			state.execute(sql);
			state.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新操作
	 * 
	 * @param tea
	 */
	public void update(Teacher tea, int id) {
		Connection con = getConn();
		try {
			// 获取Statement对象
			Statement state = con.createStatement();
			// 更新语句
			String sql = "update teacherzhang set id=" + tea.getId()
					+ ",name='" + tea.getName() + "',gender='"
					+ tea.getGender() + "',age=" + tea.getAge() + ",job='"
					+ tea.getJob() + "',createDate='" + tea.getCreatedate()
					+ "' where id=" + id + "";
			state.executeUpdate(sql);
			state.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 查询表的所有信息
	 * 
	 * @return list
	 */
	public ArrayList<Teacher> queryAll() {
		Connection con = getConn();
		ArrayList<Teacher> list = new ArrayList<Teacher>();
		try {
			// 获取Statement对象
			Statement state = con.createStatement();
			String sql = "select * from teacherzhang";
			ResultSet set = (ResultSet) state.executeQuery(sql);
			// 初始时，光标不指向任何行
			while (set.next()) {// 光标
				int id = set.getInt("id");
				String name = set.getString("name");
				String gender = set.getString("gender");
				int age = set.getInt("id");
				String job = set.getString("job");
				String createDate = set.getString("createDate");
				Teacher tea = new Teacher(id, age, name, gender, job,
						createDate);
				list.add(tea);
			}
			state.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}

package com.test.jdbc;

public class Teacher {
	private int id, age;
	private String name, gender, job, createDate;

	public Teacher(int id, int age, String name, String gender, String job,
			String createDate) {
		super();
		this.id = id;
		this.age = age;
		this.name = name;
		this.gender = gender;
		this.job = job;
		this.createDate = createDate;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	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 String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public String getCreatedate() {
		return createDate;
	}

	public void setCreatedate(String createDate) {
		this.createDate = createDate;
	}

	@Override
	public String toString() {
		return "id=" + id + "   name=" + name + "   gender=" + gender
				+ "   age=" + age + "   job=" + job + "   createDate="
				+ createDate;
	}
}

jdbc的PreparedStatement对数据库增删查改操作

package com.test.jdbcT;

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

import com.mysql.jdbc.ResultSet;

public class JDBCTest {
	public static void main(String[] args) {
		JDBCTest jc = new JDBCTest();
		// String sql =
		// "insert into teacherzhang(id,name,gender,age,job,createdate) values(110,'lhh','F',21,'teacher','2017-2-4 22:11:11')";
		// String sql = "delete from teacherzhang where id=110";
		// String sql = "update teacherzhang set name='lhh' where id=1";
		// jc.insert(sql);
		// jc.delete(sql);
		// jc.update(sql);

		// Teacher tea = new Teacher(121, 32, "gh", "F", "it",
		// "2017-5-4 22:11:11");
		// jc.insert(tea);
		// jc.delete(tea);
		// jc.update(tea, 100);
		ArrayList<Teacher> list = jc.query("lhh");
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
		}
	}

	/**
	 * 获取Connection连接对象
	 * 
	 * @return
	 */
	public Connection getConn() {
		Connection conn = null;
		try {
			// 加载
			Class.forName("com.mysql.jdbc.Driver");
			// 获取连接对象
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/lhz", "root", "lhz1314");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 插入操作
	 * 
	 * @param tea
	 */
	public void insert(Teacher tea) {
		Connection con = getConn();
		try {
			// 插入语句
			String sql = "insert into teacherzhang(id,name,gender,age,job,createdate) values(?,?,?,?,?,?)";
			PreparedStatement ps = getPre(tea, sql, con);
			// 执行
			ps.executeUpdate();
			ps.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取ps
	 * 
	 * @param tea
	 * @param sql
	 * @return
	 */
	public PreparedStatement getPre(Teacher tea, String sql, Connection con) {
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) con.prepareStatement(sql);
			ps.setInt(1, tea.getId());// 第一个?号
			ps.setString(2, tea.getName());
			ps.setString(3, tea.getGender());
			ps.setInt(4, tea.getAge());
			ps.setString(5, tea.getJob());
			ps.setString(6, tea.getCreatedate());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ps;
	}

	/**
	 * 删除操作
	 * 
	 * @param tea
	 */
	public void delete(Teacher tea) {
		Connection con = getConn();
		try {
			// 删除语句
			String sql = "delete from teacherzhang where id=?";
			PreparedStatement ps = con.prepareStatement(sql);// 不执行
			ps.setInt(1, tea.getId());
			ps.executeUpdate();
			ps.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新操作
	 * 
	 * @param tea
	 */
	public void update(Teacher tea, int id) {
		Connection con = getConn();
		try {
			// 更新语句
			String sql = "update teacherzhang set id=?,name=?,gender=?,age=?,job=?,createDate=? where id="
					+ id + "";
			PreparedStatement ps = getPre(tea, sql, con);
			ps.executeUpdate();
			ps.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 查询表的所有信息
	 * 
	 * @return list
	 */
	public ArrayList<Teacher> queryAll() {
		Connection con = getConn();
		ArrayList<Teacher> list = new ArrayList<Teacher>();
		try {
			// 获取Statement对象
			Statement state = con.createStatement();
			String sql = "select * from teacherzhang";
			ResultSet set = (ResultSet) state.executeQuery(sql);
			// 初始时，光标不指向任何行
			while (set.next()) {// 光标
				int id = set.getInt("id");
				String name = set.getString("name");
				String gender = set.getString("gender");
				int age = set.getInt("id");
				String job = set.getString("job");
				String createDate = set.getString("createDate");
				Teacher tea = new Teacher(id, age, name, gender, job,
						createDate);
				list.add(tea);
			}
			state.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 查询指定名字的信息
	 * 
	 * @param n
	 * @return
	 */
	public ArrayList<Teacher> query(String n) {
		Connection con = getConn();
		ArrayList<Teacher> list = new ArrayList<Teacher>();
		try {
			// 获取Statement对象
			Statement state = con.createStatement();
			String sql = "select * from teacherzhang where name='" + n + "'";
			ResultSet set = (ResultSet) state.executeQuery(sql);
			// 初始时，光标不指向任何行
			while (set.next()) {// 光标
				int id = set.getInt("id");
				String name = set.getString("name");
				String gender = set.getString("gender");
				int age = set.getInt("id");
				String job = set.getString("job");
				String createDate = set.getString("createDate");
				Teacher tea = new Teacher(id, age, name, gender, job,
						createDate);
				list.add(tea);
			}
			state.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}

package com.test.jdbcT;

public class Teacher {
	private int id, age;
	private String name, gender, job, createDate;

	public Teacher(int id, int age, String name, String gender, String job,
			String createDate) {
		super();
		this.id = id;
		this.age = age;
		this.name = name;
		this.gender = gender;
		this.job = job;
		this.createDate = createDate;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	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 String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public String getCreatedate() {
		return createDate;
	}

	public void setCreatedate(String createDate) {
		this.createDate = createDate;
	}

	@Override
	public String toString() {
		return "id=" + id + "   name=" + name + "   gender=" + gender
				+ "   age=" + age + "   job=" + job + "   createDate="
				+ createDate;
	}
}

实现登陆注册的逻辑（实现业务层和jdbc层的关联）

package com.test.login;

import java.util.ArrayList;
import java.util.Scanner;

/**
 * 登录注册
 * 
 * @author lhz
 * 
 */
public class Login {
	private Scanner scanner = new Scanner(System.in);

	public static void main(String[] args) {
		new Login().login();
	}

	public void login() {
		System.out.println("please input login or register:");
		String num = scanner.next();
		if (num.equals("login")) {
			System.out.println("please input your username:");
			String userName = scanner.next();
			System.out.println("please input your password:");
			String pwd = scanner.next();
			if (yanzhL(userName, pwd)) {
				System.out.println("congratulations!login successfully!");
			} else {
				System.out.println("login failure awfully!");
				login();
			}
		} else if (num.equals("register")) {
			System.out.println("please input your username:");
			String userName = scanner.next();
			System.out.println("please input your password:");
			String pwd = scanner.next();
			System.out.println(yanzhR(userName));
			if (yanzhR(userName)) {// 用户名不存在
				User user = new User(userName, pwd);
				JDBCTest.insert(user);
				System.out.println("congratulations!register successfully!");
			} else {
				System.out.println("register failure awfully!");
				login();
			}
		} else {
			System.out.println("the input is invalid. Please try again!");
			login();
		}
	}

	/**
	 * login
	 * 
	 * @param userName
	 * @param pwd
	 * @return
	 */
	public boolean yanzhL(String userName, String pwd) {
		ArrayList<User> list = JDBCTest.query(userName);
		if (list.size() == 0) {
			return false;
		}
		for (int i = 0; i < list.size(); i++) {
			User user = list.get(i);
			if (user.getPwd().equals(pwd)) {
				return true;
			}
		}
		return false;
	}

	/**
	 * register
	 * 
	 * @param userName
	 * @return
	 */
	public boolean yanzhR(String userName) {
		ArrayList<User> list = JDBCTest.query(userName);
		if (list.size() == 0) {// 没有
			return true;
		}
		return false;
	}
}

package com.test.login;

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

import com.mysql.jdbc.ResultSet;

public class JDBCTest {
	// public static void main(String[] args) {
	// JDBCTest jc = new JDBCTest();
	// User user = new User("xiaoliang", "xiaoliang");
	// // jc.insert(user);
	// // jc.delete(user);
	// // jc.update(user, "xiaowang");
	// ArrayList<User> list = jc.queryAll();
	// for (int i = 0; i < list.size(); i++) {
	// System.out.println(list.get(i));
	// }
	// }

	/**
	 * 获取Connection连接对象
	 * 
	 * @return
	 */
	private static Connection getConn() {
		Connection conn = null;
		try {
			// 加载
			Class.forName("com.mysql.jdbc.Driver");
			// 获取连接对象
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/lhz", "root", "lhz1314");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 插入操作
	 * 
	 * @param tea
	 */
	public static void insert(User user) {
		Connection con = getConn();
		try {
			// 插入语句
			String sql = "insert into user(userName,pwd) values(?,?)";
			PreparedStatement ps = getPre(user, sql, con);
			// 执行
			ps.executeUpdate();
			ps.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取ps
	 * 
	 * @param tea
	 * @param sql
	 * @return
	 */
	private static PreparedStatement getPre(User user, String sql,
			Connection con) {
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) con.prepareStatement(sql);
			ps.setString(1, user.getUserName());
			ps.setString(2, user.getPwd());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ps;
	}

	/**
	 * 删除操作
	 * 
	 * @param tea
	 */
	public static void delete(User user) {
		Connection con = getConn();
		try {
			// 删除语句
			String sql = "delete from user where userName=?";
			PreparedStatement ps = con.prepareStatement(sql);// 不执行
			ps.setString(1, user.getUserName());
			ps.executeUpdate();
			ps.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新操作
	 * 
	 * @param tea
	 */
	public static void update(User user, String u) {
		Connection con = getConn();
		try {
			// 更新语句
			String sql = "update user set userName=?,pwd=? where userName='"
					+ u + "'";
			PreparedStatement ps = getPre(user, sql, con);
			ps.executeUpdate();
			ps.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 查询表的所有信息
	 * 
	 * @return list
	 */
	public static ArrayList<User> queryAll() {
		Connection con = getConn();
		ArrayList<User> list = new ArrayList<User>();
		try {
			// 获取Statement对象
			Statement state = con.createStatement();
			String sql = "select * from user";
			ResultSet set = (ResultSet) state.executeQuery(sql);
			// 初始时，光标不指向任何行
			while (set.next()) {// 光标
				String userName = set.getString("userName");
				String pwd = set.getString("pwd");
				User userN = new User(userName, pwd);
				list.add(userN);
			}
			state.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 查询指定名字的信息
	 * 
	 * @param n
	 * @return
	 */
	public static ArrayList<User> query(String n) {
		Connection con = getConn();
		Statement state = null;
		ArrayList<User> list = new ArrayList<User>();
		try {
			// 获取Statement对象
			state = con.createStatement();
			String sql = "select * from user where userName='" + n + "'";
			ResultSet set = (ResultSet) state.executeQuery(sql);
			// 初始时，光标不指向任何行
			while (set.next()) {// 光标
				String userName = set.getString("userName");
				String pwd = set.getString("pwd");
				User userN = new User(userName, pwd);
				list.add(userN);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				state.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
}

package com.test.login;

public class User {
	private String userName, pwd;

	public User(String userName, String pwd) {
		super();
		this.userName = userName;
		this.pwd = pwd;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getPwd() {
		return pwd;
	}

	public void setPwd(String pwd) {
		this.pwd = pwd;
	}

	@Override
	public String toString() {
		return "userName=" + userName + "   pwd=" + pwd;
	}
}