JDBC
全称 java database connectivity 通过Java代码来操作数据库。
sun公司定义出一套完整的接口,这些接口是有数据库厂商去实现,俗称为驱动
JDBC常见接口三个接口,一个类
DriverManager 类 管理驱动
Connetion 接口 连接对象
Statement 接口 执行sql语句
ResultSet 接口 获取的结果集 查询
JDBC入门
这里使用eclipse来说明使用方法
步骤:
1.建立java项目,导入jar包 新建lib文件夹,鼠标右键build path -->add to build path
2.加载驱动 (对应的数据库厂商提供的驱动)
3.获取连接对象
4.执行sql语句
5.遍历结果集
6.关闭资源
public static void main(String[] args) throws Exception {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象 设置编码方式
String url="jdbc:mysql://localhost:3306/databaseName?useUnicode=true&characteEncoding=utf-8";
Connection connection = DriverManager.getConnection(url, "root", "123456");
//3.执行sql
Statement sm = connection.createStatement();
//4.执行sql
ResultSet rs = sm.executeQuery("select*from stu");
while(rs.next()) {//判断有没有下一行数据
System.out.println(rs.getString("sname"));
}
} //这里没有关闭资源 实际是需要的
进一步
public static void main(String[] args) throws Exception {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库
/*
* String url="jdbc:mysql://IP:端口号/数据库名字"
* */
String url="jdbc:mysql://127.0.0.1:3306/databaseName";
Connection con=DriverManager.getConnection(url, "root", "123456");
//3.执行sql
Statement sm=con.createStatement();
ResultSet rs=sm.executeQuery("select*from stu");
//遍历结果集,结果可能有很多行,每一行有很多列
while(rs.next()) {//每执行next()方法一次,获取一行记录
//列号从1开始
int id=rs.getInt(1);
String name=rs.getString(2);
System.out.println(id+"---"+name);
}
}
JDBC实现增删改查
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
public class JDBC_CRUD {
/*
* @Before public void init() { System.out.println("初始化"); }
*/
public void testAdd() throws Exception {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立连接
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseName?useUnicode=true&characterEncoding=utf-8",
"root", "123456"); //root为账号 后面是密码
//3.执行sql
Statement sm=con.createStatement();
int row=sm.executeUpdate("insert stu(sid,sname)values(null,'李五')");
System.out.println("影响了"+row+"行");
//4关闭资源
con.close();
sm.close();
}
public void testDel() throws Exception {
//1加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2建立连接 127.0.0.1相当于localhost 都是本机
Connection con=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/java0708?useUnicode=true&characterEncoding=utf-8",
"root", "123456");
//3.执行sql
Statement sm=con.createStatement();
int row=sm.executeUpdate("delete from stu where sid=45");
System.out.println("删除了"+row+"行");
//4关闭资源
con.close();
sm.close();
}
public void testUpdate() throws Exception {
//1.加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//2.建立连接
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseName?useUnicode=true&characterEncoding=utf-8",
"root", "123456");
//3执行sql
Statement sm=con.createStatement();
int row=sm.executeUpdate("update stu set sname='芙' where sname='李'");
System.out.println("修改了"+row+"行");
//4.关闭资源
con.close();
sm.close();
}
@Test
public void testQuery() throws Exception {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2,建立连接
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseName?useUnidode=true&characterEncoding=utf-8",
"root", "123456");
//3.执行sql
Statement sm=con.createStatement();
ResultSet rs=sm.executeQuery("select *from stu");
//4.遍历结果集
while(rs.next()) {//获取一行记录
int id= rs.getInt(1);
String name=rs.getString(2);
System.out.println("id="+id+"name="+name);
}
//5.关闭资源
con.close();
sm.close();
rs.close();
}
}
通过读取properties文件 封装JDBC工具类
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.imageio.stream.FileImageInputStream;
public class JdbcUtil {
public static String DRIVERCLASSNAME;
public static String URL;
public static String USERNAME;
public static String PASSWORD;
static {
Properties p=new Properties();
try {
p.load(new FileInputStream("src/jdbc.properties"));
DRIVERCLASSNAME=p.getProperty("driverClassName");
URL=p.getProperty("url");
USERNAME=p.getProperty("username");
PASSWORD=p.getProperty("password");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
Class.forName(DRIVERCLASSNAME);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection con=null;
try {
con=DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
public static void close(Connection con,Statement sm,ResultSet rs) {
try {
if (null!=con) {
con.close();
}
if (null!=sm) {
sm.close();
}
if (null!=rs) {
rs.close();
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
}