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;}@Overridepublic 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;}@Overridepublic 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;}@Overridepublic String toString() {return "userName=" + userName + "   pwd=" + pwd;}}