一、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;
}
}