什么叫JDBC?
Java DataBase Connectity(java数据库连接)
操作数据库步骤
1.注册驱动
class.forname("com.mysql.jdbc.Driver")
2.连接数据库
//格式:协议+:+连接的数据库+://+IP地址+:+端口号+/+数据库名+?+时区+&+使用Unicode编码+&+字符集
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/j2005_db?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8")
3.编写sql语句
String sql="";
4.使用Statment提交sql语句到数据库
Statement stmt=conn.createStament();
5.获取结果
如果是查询
ResultSet rs=stmt.executeQuery(sql);
如果是增删改
int n=stmt.executeUpdate(sql);//n为数据库中受影响的行数
6.判断是否修改成功
如果是增删改
System.out.println(n>"插入成功":"插入失败");
如果是查询
//需要知道列名字段
while(rs.next())
{
int column1=rs.getInt("id");
String column2=rs.getString("name");
System.out.println(column1+","+column2);
}
7.关闭连接
rs.close();
stmt.close();
conn.close();
由于每次操作数据库都需要进行同样的步骤,因此将连接数据库的步骤封装为工具类。
package com.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBHelper {
//将连接数据库需要用到的变量全部定义为全局变量
//格式:协议+:+连接的数据库+://+IP地址+:+端口号+/+数据库名+?+时区+&+使用Unicode编码+&+字符集
public static final String URL="jdbc:mysql://localhost:3306/j2005_db?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8";
public static final String DRIVER = "com.mysql.jdbc.Driver";
public static final String USER="root";
public static final String PASSWORD ="123456";
private static Connection conn;
//驱动只用在加载类的时候调用一次就行,因此可以使用静态块
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//将连接操作封装
public static synchronized Connection getConn()
{
if(conn==null)
{
try {
conn=DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
}
return conn;
}
//将关闭操作封装
public static void close(Connection conn,Statement st,ResultSet rs) {
try {
if(rs!=null)
{
rs.close();
}
if(st!=null)
{
st.close();
}
if(conn!=null)
{
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
获取自动增长id
public int m1(HeroDTO hero) throws Exception {
// 获取连接
Connection conn = HeroUtil.getConn();
// 当插入一条数据之后,获取自动增长的id
String sql = "insert into hero(id,name,nickName,job,ad,ap,df,story) values (0,?,?,?,?,?,?,?)";
// 创建预编译处理对象,先编译sql语句
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, hero.getName());
ps.setString(2, hero.getNickName());
ps.setString(3, hero.getJob());
ps.setInt(4, hero.getAd());
ps.setInt(5, hero.getAp());
ps.setInt(6, hero.getDf());
ps.setString(7, hero.getStory());
// 执行sql语句
int n = ps.executeUpdate();
int id = -1;
if (n > 0) {
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
// 1代表,第1列
id = rs.getInt(1);
// 能够自动增长的字段,一定是int
System.out.println(id);
}
}
HeroUtil.close(conn, ps, null);
return id;
}
事务处理
事务处理就是为了保证业务逻辑之间同时成功或者同时失败。
account实体类:
package com.pojo.account;
public class Account {
private int actno;//账户名称
private double balance;//账户余额
public Account(int actno, double balance) {
super();
this.actno = actno;
this.balance = balance;
}
public Account() {
super();
}
public int getActno() {
return actno;
}
public void setActno(int actno) {
this.actno = actno;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
@Override
public String toString() {
return "Account [actno=" + actno + ", balance=" + balance + "]";
}
}
account操作类:
package com.pojo.account;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
/**
* @author yuhang
*
*/
public class AccountDAO {
public boolean zz(int p1,int p2,double money) throws Exception {
boolean flag=false;//默认转账失败
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/j2005_db","root","123456");
//设置不自动提交(在调用提交命令之前的内容是不提交)
conn.setAutoCommit(false);
//编写sql语句
String sql1="update tb_account set balance=balance-? where actno=?";
//将sql语句提交到数据库服务器
PreparedStatement ps1=conn.prepareStatement(sql1);
//设置预编译中占位符的数值
ps1.setDouble(1, money);
ps1.setInt(2, p1);
String sql2="update tb_account set balance=balance+? where actno=?";
PreparedStatement ps2=conn.prepareStatement(sql2);
ps2.setDouble(1, money);
ps2.setInt(2, p2);
//执行sql语句
ps1.executeUpdate();
ps2.executeUpdate();
//提交,结束事务
conn.commit();
flag=true;
//关闭
conn.close();
ps1.close();
ps2.close();
return flag;
}
}
测试类:
package com.pojo.account;
import junit.framework.TestCase;
public class AccountDAOTest extends TestCase {
public void testZz() throws Exception {
AccountDAO dao =new AccountDAO();
boolean flag=dao.zz(2, 1, 100);
System.out.println(flag);
}
}
简单的事务实例:删除数据
package demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;
public class DemoDAO {
private int id;
private String name;
public boolean delete(int rows) throws Exception {
boolean flag=false;
Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/j2005_db","root","123456");
conn.setAutoCommit(false);
String sql="delete from tb_demo1 where id=?";
PreparedStatement ps=null;
for(int i=0;i<= rows;i++)
{
ps=conn.prepareStatement(sql);
ps.setInt(1, i);
ps.executeUpdate();
}
System.out.println("请输入是否执行操作:YES ? NO?");
String h=new Scanner(System.in).next();
if("yes".equalsIgnoreCase(h))
{
conn.commit();
flag=true;
}
else
conn.rollback();
conn.close();
ps.close();
return flag;
}
}