JDBC
1.什么是JDBC?
JDBC就是java数据库连接,说白了就是用java语言来操作数据库,JDBC使用java语言向数据库中发送SQL语句。
导入Jar包,加载驱动类,给出url、user、password。
使用DriverManager类得到connection对象。
jar包地址:mysql-connector-java-5.1.47.jar
package com.forcoldplay.test2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.junit.Test;
public class Demo013 {
@Test
public void fun1() throws ClassNotFoundException, SQLException {
/*
* jdbc四大配置参数
* driverClassName:com.mysql.jdbc.Driver
* url: jdbc:mysql://localhost:3306/test
* username:root
* password:123
*/
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "123";
Connection con = DriverManager.getConnection(url,username,password);
System.out.println(con);
}
}
2.JDBC的增删改查
1.通过Connection对象创建Statement
Statement语句的发送器,功能是向数据库发送sql语句
2.调用它的int executeUpdate(String sql) ,它可以发送DML,DDL
增删改
package com.forcoldplay.test2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class Demo013 {
@Test
public void fun1() throws ClassNotFoundException, SQLException {
/*
* jdbc四大配置参数
* driverClassName:com.mysql.jdbc.Driver
* url: jdbc:mysql://localhost:3306/test
* username:root
* password:123
*/
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "123";
Connection con = DriverManager.getConnection(url,username,password);
System.out.println(con);
//通过Connection 得到Statement对象
Statement stmt = con.createStatement();
//操作test数据库里的stu6,不能在语句中加分号
//String sql = "INSERT INTO stu6 VALUES(3,'wangwu')";
//String sql = "UPDATE stu6 SET sname = 'suolong' WHERE sid = 3";
String sql = "DELETE FROM stu6";
int r = stmt.executeUpdate(sql);
//返回的r为影响结果的数量
System.out.println(r);
}
}
执行查询
package com.forcoldplay.test2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class Demo013 {
public void fun1() throws ClassNotFoundException, SQLException {
/*
* jdbc四大配置参数
* driverClassName:com.mysql.jdbc.Driver
* url: jdbc:mysql://localhost:3306/test
* username:root
* password:123
*/
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "123";
Connection con = DriverManager.getConnection(url,username,password);
System.out.println(con);
//通过Connection 得到Statement对象
Statement stmt = con.createStatement();
//操作test数据库里的stu6,不能在语句中加分号
String sql = "INSERT INTO stu6 VALUES(4,'zhangsan')";
//String sql = "UPDATE stu6 SET sname = 'suolong' WHERE sid = 3";
//String sql = "DELETE FROM stu6";
int r = stmt.executeUpdate(sql);
//返回的r为影响结果的数量
System.out.println(r);
}
@Test
public void fun2() throws ClassNotFoundException, SQLException {
/*
* jdbc四大配置参数
* driverClassName:com.mysql.jdbc.Driver
* url: jdbc:mysql://localhost:3306/test
* username:root
* password:123
*/
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "123";
Connection con = DriverManager.getConnection(url,username,password);
System.out.println(con);
//通过Connection 得到Statement对象
Statement stmt = con.createStatement();
//调用Statement的ResultSet rs = executeQuery(String querySql);
ResultSet rs = stmt.executeQuery("SELECT * FROM stu6");
//解析ResultSet
//其内部有个行光标
//提供了一个系列的getXxx()方法
//把行光标移动到first
while(rs.next()) { //把光标移动到下一行,并判断下一行是否存在
int sid = rs.getInt("sid");
String sname = rs.getString(2);
System.out.println(sid+","+sname);
}
/*
* 关闭资源,倒关
*
*/
rs.close();
stmt.close();
con.close();//必须要关
}
}
3.JDBC之代码规范
关闭资源有可能因为异常提前终止而执行不到,所以应该在fanlly块中
//规范化
@Test
public void fun3() throws SQLException {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
String driverClassName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "123";
Class.forName(driverClassName);
con = DriverManager.getConnection(url,username,password);
stmt = con.createStatement();
String sql ="SELECT * FROM stu6";
rs = stmt.executeQuery(sql);
//遍历rs ,打出其中数据
while(rs.next()) {
int sid = rs.getInt(1);
String sname = rs.getNString("sname");
System.out.println(sid + "," + sname);
}
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
//关闭
if(rs!=null) rs.close();
if(stmt!=null) stmt.close();
if(con!=null) con.close();//必须要关
}
}
4. 结果集光标
ResultSet
void beforeFirst():把光标放在第一行的前面
void afterLast():把光标放在最后一行的后面
boolean first():把光标放在第一行的位置上
boolean last():把当前光标放到最后一行的位置上
boolean previous():把光标向上挪一行
boolean next(): 把光标向下挪一行
5.结果集的特性
是否可滚动
是否敏感
是否可更新