文章目录
JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API ，
JDBC提供了一种基准,据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。
JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。
我们使用的是mysql的驱动mysql jar包
为了简化、统一对数据库的操作，定义了一套Java操作数据库的规范（接口），称之为JDBC。这套接口由数据库厂商去实现，这样，开发人员只需要学习jdbc接口，并通过jdbc加载具体的驱动，就可以操作数据库。
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
id INT(4) not null,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday date,
PRIMARY KEY (`id`)
);
INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.用户信息和url（useUnicode=true&characterEncoding=utf-8表示设置数据库编码为utf-8，useSSL=true表示安全连接）
String url=("jdbc:mysql://localhost/jdbcstudy?useUnicode=true&characterEncoding=utf-8&useSSL=true");
String username="root";
String password="root";
//3.连接成功，创建数据库对象，connection是数据库的对象，他可以完成很多数据库操作
Connection connection = DriverManager.getConnection(url,username,password);
connection.rollback(); //事务回滚
connection.commit()； //事务提交
connection,setAutoCommit //设置自动提交
//4.执行SQL的对象 Statement 执行sql的对象；
Statement statement = connection.createStatement();
statement.executeQuery(); //查询操作返回 ResultSet
statement.execute(); //执行任何sql
statement.executeUpdate //执行更新、删除、修改操作，返回一个受影响的行数
//5.执行sql语句，查看返回结果
String sql ="select * from users";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));//不知道数据类型时使用Object
System.out.println("name="+resultSet.getString("NAME"));
System.out.println("Pwd="+resultSet.getString("PASSWORD"));
System.out.println("email="+resultSet.getString("email"));
System.out.println("birth="+resultSet.getDate("birthday"));
}
//6.关闭连接，释放资源
resultSet.close();
statement.close();
connection.close();
}
在项目下新建一个util包，然后建一个Jdbcutil类来封装数据库连接的代码 public class JdbcUtil {
//获取数据库连接
private String jdbcName=("com.mysql.jdbc.Driver");
private String url =("jdbc:mysql://localhost/jdbcstudy?useUnicode=true&characterEnding=utf-8&useSSL=true");
private String userName=("root");
private String password=("root");
public Connection getConnection(){
Connection con =null;
try {
Class.forName(jdbcName);
con= DriverManager.getConnection(url,userName,password);
Statement statement = con.createStatement();
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
return con;
}
//释放资源
public static void release(Connection con, Statement sta, ResultSet rs){
if(rs!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(sta!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public class JdbcTest {
public static void main(String[] args) throws SQLException {
ResultSet rs =null;
Connection conn =null;
Statement sta =null;
JdbcUtil jdbcUtil = new JdbcUtil();
conn= jdbcUtil.getConnection();
Statement statement = conn.createStatement();
String sql = "select * from users";
rs= statement.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getInt("id"));
}
jdbcUtil.release(conn,sta,rs);
}
}
public class JdbcTest {
public static void main(String[] args) {
ResultSet rs =null;
Connection conn =null;
Statement sta =null;
JdbcUtil jdbcUtil = new JdbcUtil();
conn= jdbcUtil.getConnection();
try {
sta = conn.createStatement();
String sql = "insert into `users` (`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
"values(4,'李冲','23123123',concat(floor(rand()*100000),\"@qq.com\"),'2000-2-12')";
int i = sta.executeUpdate(sql);
if(i>0){
System.out.println("插入成功！！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtil.release(conn,sta,rs);
}
}}
public class JdbcDelete {
public static void main(String[] args) {
ResultSet rs =null;
Connection conn =null;
Statement sta =null;
JdbcUtil jdbcUtil = new JdbcUtil();
conn= jdbcUtil.getConnection();
try {
sta = conn.createStatement();
String sql = "delete from users where `id`=4";
int i = sta.executeUpdate(sql);
if(i>0){
System.out.println("删除成功！！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtil.release(conn,sta,rs);
}
}}
public class JdbcUpdate {
public static void main(String[] args) {
ResultSet rs =null;
Connection conn =null;
Statement sta =null;
JdbcUtil jdbcUtil = new JdbcUtil();
conn= jdbcUtil.getConnection();
try {
sta = conn.createStatement();
String sql = "update users set `NAME`='lihaugn' where id =3";
int i = sta.executeUpdate(sql);
if(i>0){
System.out.println("更新成功！！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtil.release(conn,sta,rs);
}
}
}
public class JdbcSelect {
public static void main(String[] args) {
ResultSet rs =null;
Connection conn =null;
Statement sta =null;
JdbcUtil jdbcUtil = new JdbcUtil();
conn= jdbcUtil.getConnection();
try {
sta = conn.createStatement();
String sql = "select * from users";
rs = sta.executeQuery(sql);
while(rs.next()){
System.out.println("id="+rs.getInt("id"));
System.out.println("name="+rs.getString("NAME"));
System.out.println("email"+rs.getString("email"));
System.out.println("birthday"+rs.getString("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtil.release(conn,sta,rs);
}
}
}