JDBC步骤
1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
2、通过驱动管理器获取数据库链接 (数据库地址, 用户名、密码) 127.0.0.1 = localhost url格式:jdbc:mysql://数据库服务IP:端口号/数据库名称
conn = DriverManager.getConnection(url, "root", "root");
3、使用数据库链接操作数据
stat = conn.createStatement();
String sql = "update stu set score = 60 where stu_no = '20190715129'";
int affectedRows = stat.executeUpdate(sql);
if(affectedRows > 0) {
System.out.println("=================>数据修改成功!");
}
4、关闭连接
conn.close();
stat.close();
JDBC接口
JDBC中定义了一些接口:
- 驱动管理:
DriverManager - 连接接口
Connection
Connection接口负责应用程序对数据库的连接,在加载驱动之后,使用url、username、password三个参数,创建到具体数据库的连接。
Class.forName("oracle.jdbc.OracleDriver")
//根据url连接参数,找到与之匹配的Driver对象,调用其方法获取连接
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.0.26:1521:tarena",
"openlab","open123");
DatabasemetaData
- 语句对象接口
Statement
Statement接口用来处理发送到数据库的SQL语句对象,通过Connection对象创建。主要有三个常用方法:
Statement stmt=conn.createStatement();
//1.execute方法,如果执行的sql是查询语句且有结果集则返回true,如果是非查询语句或者没有结果集,返回false
boolean flag = stmt.execute(sql);
//2.执行查询语句,返回结果集
ResultSetrs = stmt.executeQuery(sql);
//3.执行DML语句,返回影响的记录数
int flag = stmt.executeUpdate(sql);
PreparedStatement
CallableStatement
- 结果集接口
ResultSet
执行查询SQL语句后返回的结果集,由ResultSet接口接收。
常用处理方式:遍历 / 判断是否有结果(登录)。
String sql = "select * from emp";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getInt("empno")+",“
+rs.getString("ename") );
}
ResultSetMetaData
JDBC案例
//查询数据
public static void queryData() throws ClassNotFoundException {
String url = "jdbc:mysql://127.0.0.1:3306/test";
String sql = "select * from stu";
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(url, "root", "root");
stat = conn.createStatement();
rs = stat.executeQuery(sql);
if(stat.execute(sql)) {//execute 既可以处理查询,也可以处理数据更新
rs = stat.getResultSet();
while(rs.next()) {//rs.next() 表示将指针向下移动,如果有数据返回true, 如果没有数据返回false |column : 栏位
System.out.print(rs.getString(1) + "<---------->" + rs.getString("stu_no") + " | ");
System.out.print(rs.getString(2) + "<---------->" + rs.getString("stu_name") + " | ");
System.out.print(rs.getInt(3) + "<---------->" + rs.getInt("gender") + " | ");
System.out.print(rs.getInt(4) + "<---------->" + rs.getInt("age") + " | ");
System.out.print(rs.getInt(5) + "<---------->" + rs.getInt("score") + " | ");
System.out.print(rs.getString(6) + "<---------->" + rs.getString("major") + "\n");
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(rs != null) {
rs.close();
}
if(stat != null) {
stat.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//增加数据
public static void insertData() {
String url = "jdbc:mysql://127.0.0.1:3306/test";
Connection conn = null;
Statement stat = null;
//1、加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
//2、通过驱动管理器获取数据库链接 (数据库地址, 用户名、密码) 127.0.0.1 = localhost url格式:jdbc:mysql://数据库服务IP:端口号/数据库名称
conn = DriverManager.getConnection(url, "root", "root");
//3、使用数据库链接操作数据
stat = conn.createStatement();
String sql = "insert into stu values('20190921123', '小辉', 1, 19, 78, '软件工程')";
int affectedRows = stat.executeUpdate(sql);//通过Statement对象.executeUpdate(sql) 更新数据库数据:添加数据、修改数据、删除数据
if(affectedRows > 0) {
System.out.println("=================>数据添加成功!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//4、关闭连接
try {
if(stat != null) {
stat.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//修改数据
public static void updateData() {
String url = "jdbc:mysql://127.0.0.1:3306/test";
Connection conn = null;
Statement stat = null;
//1、加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
//2、通过驱动管理器获取数据库链接 (数据库地址, 用户名、密码) 127.0.0.1 = localhost url格式:jdbc:mysql://数据库服务IP:端口号/数据库名称
conn = DriverManager.getConnection(url, "root", "root");
//3、使用数据库链接操作数据
stat = conn.createStatement();
String sql = "updata stu set score = 60 where stu_no = '20190715129'";
int affectedRows = stat.executeUpdate(sql);//通过Statement对象.executeUpdate(sql) 更新数据库数据:添加数据、修改数据、删除数据
if(affectedRows > 0) {
System.out.println("=================>数据修改成功!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//4、关闭连接
try {
if(stat != null) {
stat.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//登录
public static void login(String username, String passwd) throws ClassNotFoundException {
String url = "jdbc:mysql://127.0.0.1:3306/test";
String sql = "select * from user_info where user_name = '" + username + "' and password= '" + passwd +"'";
System.out.println("sql---------------->" + sql);
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(url, "root", "root");
stat = conn.createStatement();
rs = stat.executeQuery(sql);
if(rs.next()) {
System.out.println("用户登录成功!!");
}else {
System.out.println("用户登录失败!!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(rs != null) {
rs.close();
}
if(stat != null) {
stat.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void loginWithPrepare(String username, String passwd) throws ClassNotFoundException {
String url = "jdbc:mysql://127.0.0.1:3306/test";
String sql = "select * from user_info where user_name = ? and password= ?";
System.out.println("sql---------------->" + sql);
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(url, "root", "root");
stat = conn.prepareStatement(sql);//
stat.setString(1, username);
stat.setString(2, passwd);
rs = stat.executeQuery();
if(rs.next()) {
System.out.println("用户登录成功!!");
}else {
System.out.println("用户登录失败!!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(rs != null) {
rs.close();
}
if(stat != null) {
stat.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//删除数据
public static void delStu() {
Connection conn = DbUtil.getDBConn();
Statement stat = null;
try {
stat = conn.createStatement();
stat.executeUpdate("delete from stu where stu_no = '20180921123'");
} catch (SQLException e) {
e.printStackTrace();
}finally {
DbUtil.close(conn, stat);
}
}
//查找所有数据
public static List<Student> queryAllStu(){
List<Student> stuList = new LinkedList<Student>();
Connection conn = DbUtil.getDBConn();
Statement stat = null;
ResultSet rs = null;
try {
stat = conn.createStatement();
rs = stat.executeQuery("select * from stu");
Student stu = null;
while(rs.next()) {
stu = new Student(rs.getString(1), rs.getString(2), rs.getInt(4), rs.getInt(3), rs.getInt(5));
stuList.add(stu);
}
} catch (SQLException e) {
e.printStackTrace();
}return stuList;
}
DbUtil
public class DbUtil {
private final static String DB_USERNAME = "root";
private final static String DB_PASSWD = "root";
private final static String DB_URL = "jdbc:mysql://localhost:3306/test";
public static Connection getDBConn() {
try {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection conn) {
try {
if(conn != null) {
conn.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
public static void close(Statement stat) {
try {
if(stat != null) {
stat.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
public static void close(Connection conn, Statement stat, ResultSet rs) {
close(rs);
close(stat);
close(conn);
}
public static void close(Connection conn, Statement stat) {
close(stat);
close(conn);
}}