jdbc连接数据库
package com.wei.lesson01;
import java.sql.*;
public class jdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.用户信息和URL
String url ="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC";
String usename ="root";
String pwd ="1234";
//3.连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, usename, pwd);
//4执行SQL对象
Statement statement = connection.createStatement();
//5.执行SQL的对象,去 执行SQL
String sql ="select *from student";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("学号:"+resultSet.getObject("studentno"));
System.out.println("姓名:"+resultSet.getObject("studentname"));
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
1.加载驱动
2.连接数据库
3.获得执行sql的对象statement
4.获得返回的结果集
5.释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, usename, pwd);
//对象 connection 代表数据库
//事务回滚
//事务提交
//数据库设置自动提交
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url ="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC";
//mysql --3306
//协议://主机地址:端口号/数据库名?参数1&参数2&参数3
//Oralce --1521
//jdbc:Oracle:thin:@localhost:1521:sid
statement
jdbc封装
- db.properties文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&charsetacterEncoding =utf8&useSSl=true
usename=root
password=1234
2.封装的类 JdbcUtils
package com.wei.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver=null;
private static String url=null;
private static String usename=null;
private static String password=null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");//读取db.properties文件
Properties properties = new Properties(); //Properties读取配置的
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
usename = properties.getProperty("usename");
password = properties.getProperty("password");
//1.驱动只加载一次
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConection() throws SQLException {
return DriverManager.getConnection(url,usename,password);
}
//释放资源
public static void release(Connection conn, Statement st,ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.利用封装好的类插入数据
package com.wei.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class testinsert {
public static void main(String[] args) {
Connection conection=null;
Statement st=null;
ResultSet rs=null;//因为需要释放资源[close()],所以提出来
try {
conection = JdbcUtils.getConection();
st = conection.createStatement();
String sql ="insert into `account`(`id`,`name`,`money`) values (4,'wst',10000000),(5,'A',10000),(6,'B',10000);";
int i = st.executeUpdate(sql);
System.out.println("插入成功");
} catch (SQLException e) {
e.printStackTrace();
}finally {//释放资源
JdbcUtils.release(conection,st,null);
}
}
}
4.删除数据
package com.wei.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conection=null;
Statement st=null;
ResultSet rs=null;//因为需要释放资源[close()],所以提出来
try {
conection = JdbcUtils.getConection();
st = conection.createStatement();
String sql ="delete from account where id =5";
int i = st.executeUpdate(sql);
System.out.println("删除成功");
} catch (SQLException e) {
System.out.println("出现异常");
e.printStackTrace();
}finally {//释放资源
JdbcUtils.release(conection,st,null);
}
}}
5.修改
package com.wei.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdata {
public static void main(String[] args) {
Connection conection=null;
Statement st=null;
ResultSet rs=null;//因为需要释放资源[close()],所以提出来
try {
conection = JdbcUtils.getConection();
st = conection.createStatement();
String sql ="update account set name='C' where id =3";
int i = st.executeUpdate(sql);
System.out.println("修改成功");
} catch (SQLException e) {
System.out.println("出现异常");
e.printStackTrace();
}finally {//释放资源
JdbcUtils.release(conection,st,null);
}
}
}
6.查询
package com.wei.lesson02;
import com.wei.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Testselect {
public static void main(String[] args) {
Connection conection=null;
Statement st=null;
ResultSet rs=null;//因为需要释放资源[close()],所以提出来
try {
conection = JdbcUtils.getConection();
st = conection.createStatement();
String sql ="select * from account where id =1";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getObject("id"));
System.out.println(rs.getObject("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {//释放资源
JdbcUtils.release(conection,st,null);
}
}
}
使用prepareStatement
package com.wei.lesson02;
import com.wei.lesson02.utils.JdbcUtils;
import java.sql.*;
public class testinsert2 {
public static void main(String[] args) {
Connection conection=null;
PreparedStatement st=null;
ResultSet rs=null;//因为需要释放资源[close()],所以提出来
try {
conection = JdbcUtils.getConection();
String sql ="insert into `account`(`id`,`name`,`money`) values (?,?,?);";
st = conection.prepareStatement(sql);//预编译不执行
//手动给参数赋值,利用参数下标注入
st.setInt(1,8);
st.setString(2,"李泉");
st.setFloat(3,10000);
int i = st.executeUpdate();//执行
System.out.println("插入成功");
} catch (SQLException e) {
System.out.println("出现异常");
e.printStackTrace();
}finally {//释放资源
JdbcUtils.release(conection,st,null);
}
}
}
prepareStatement 能防止SQL注入
package com.wei.lesson02;
import com.wei.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Testselect3 {
public static void main(String[] args) {
login("'' or '1=1'");
}
public static void login(String name){
Connection conection=null;
PreparedStatement st=null; //预编译PreparedStatement更好
ResultSet rs=null;//因为需要释放资源[close()],所以提出来
try {
conection = JdbcUtils.getConection();
//PreparedStatement防止SQL注入的本质是,把传递进来的参数当做字符(占位符?代替)
//假设传进来的参数有能 ' 会被直接转义
String sql ="select * from account where `name` =?";//?占位符代替参数
st = conection.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
//手动给参数赋值
st.setObject(1,name);
//执行SQL
rs = st.executeQuery();
while (rs.next()){
System.out.println(rs.getObject("name"));
System.out.println(rs.getObject("money"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {//释放资源
JdbcUtils.release(conection,st,rs);
}
}}
jdbc操作事务(事务要么都成功,要么都失败)
步骤:1开启事务
2一组事务执行完毕提交事务
3.可在catch语句中显示定义回滚语句,但默认
package com.wei.lesson03;
import com.wei.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransactiao1 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn = JdbcUtils.getConection();
//关闭事务自动提交,自动会开启事务
conn.setAutoCommit(false);
String sql1 ="update account set money =money-100 where id=?";
st = conn.prepareStatement(sql1);
st.setInt(1,1);
st.executeUpdate();
int x=1/0;//用于模拟事务故障
String sql2 ="update account set money =money+100 where id=?";
st = conn.prepareStatement(sql2);
st.setInt(1,8);
st.executeUpdate();
conn.commit();
conn.setAutoCommit(true);
System.out.println("修改成功");
} catch (SQLException e) {
try {
conn.rollback();//可以省略
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}