jdbc_test
回滚
public class jdbc_test {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection conn = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
try {
//获取连接
conn = JDBCUtils.getConnection();
//开启事务
conn.setAutoCommit(false);
//定义sql
String sql1 = "update a2 set balance = balance - ? where id = ?";
String sql2 = "update a2 set balance = balance + ? where id = ?";
//获取执行Sql对象
pstmt1 = conn.prepareStatement(sql1);
pstmt2 = conn.prepareStatement(sql2);
//设置参数
pstmt1.setDouble(1, 500);
pstmt1.setInt(2, 1);
pstmt1.setInt(1, 500);
pstmt1.setInt(2, 2);
//执行sql
pstmt1.executeUpdate();
//手动制造一个异常
int i = 3 / 0;
pstmt2.executeUpdate();
//提交事务
conn.commit();
}catch(Exception e){
try{
if(conn!=null){
conn.rollback();
}
}catch(SQLException e1){
e1.printStackTrace();
}
e.printStackTrace();
}finally {
JDBCUtils.close(pstmt1,conn);
JDBCUtils.close(pstmt2,conn);
}
}
}
登陆
public boolean login(String username,String password) throws SQLException {
if(username==null||password==null){
return false;
}
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
conn = JDBCUtils.getConnection();
//定义sql
String sql ="select * from a1 where name = '"+username+"' and password = '"+password+"'";
//获取执行sql对象
stmt = conn.createStatement();
//执行查询
rs = stmt.executeQuery(sql);
return rs.next();
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.close(rs,stmt,conn);
}
return false;
}
注册
public boolean register(String username,String password) throws SQLException {
boolean a=false;
if(username == null || password == null) {
return a;
}
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try {
conn = JDBCUtils.getConnection();
//确认该账号是否已被注册 查询语句
//定义sql
String sql1 = "select * from a1 where name = '"+username+"'";
//获取执行sql对象
stmt = conn.createStatement();
//执行查询
rs=stmt.executeQuery(sql1);
//开始注册 插入语句
//定义sql
String sql = "INSERT INTO a1 (name,password) VALUES ('"+username+"','"+password+"')";
//获取执行sql对象
stmt = conn.createStatement();
//执行插入
if(stmt.executeUpdate(sql)>=1)
a=true;
return a;
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.close(stmt,conn);
JDBCUtils.close(rs,stmt,conn);
}
return a;
}
全部
public class jdbc_test {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.导入jar包
//2.注册驱动
// Class.forName("com.mysql.jdbc.Driver");//alt+回车
//3.获取数据库连接对象 Connection
// Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/a","root","123456");
//4.定义sql
// String sql = "update a1 set name = 'xyz1' where id=2";
//5.获取执行sql语句对象,Statement
// Statement statement = connection.createStatement();
//6.执行sql语句,接收返回结果
// int count = statement.executeUpdate(sql);
//7.处理结果
// System.out.println(count);
//8.释放资源
// statement.close();
// connection.close();
// Connection conn = null;
// PreparedStatement pstmt1 = null;
// PreparedStatement pstmt2 = null;
// try {
// //获取连接
// conn = JDBCUtils.getConnection();
// //开启事务
// conn.setAutoCommit(false);
// //定义sql
// String sql1 = "update a2 set balance = balance - ? where id = ?";
// String sql2 = "update a2 set balance = balance + ? where id = ?";
// //获取执行Sql对象
// pstmt1 = conn.prepareStatement(sql1);
// pstmt2 = conn.prepareStatement(sql2);
// //设置参数
// pstmt1.setDouble(1, 500);
// pstmt1.setInt(2, 1);
// pstmt1.setInt(1, 500);
// pstmt1.setInt(2, 2);
//
// //执行sql
// pstmt1.executeUpdate();
// //手动制造一个异常
// int i = 3 / 0;
// pstmt2.executeUpdate();
// //提交事务
// conn.commit();
// }catch(Exception e){
// try{
// if(conn!=null){
// conn.rollback();
// }
// }catch(SQLException e1){
// e1.printStackTrace();
// }
// e.printStackTrace();
// }finally {
// JDBCUtils.close(pstmt1,conn);
// JDBCUtils.close(pstmt2,conn);
// }
// }
Scanner sc = new Scanner(System.in);
//登陆
// System.out.println("请输入用户名:");
// String username = sc.nextLine();
// System.out.println("请输入密码");
// String password = sc.nextLine();
// //调用方法
// boolean flag = new jdbc_test().login(username,password);
// if(flag){
// System.out.println("登陆成功");
// }
// else{
// System.out.println("登陆失败");
// }
//注册
System.out.println("请输入用户名:");
String username = sc.nextLine();
System.out.println("请输入密码:");
String password = sc.nextLine();
System.out.println("请确认密码:");
String repassword = sc.nextLine();
if (password.equals(repassword)) {
//调用方法
boolean flag = new jdbc_test().register(username, password);
if (flag) {
System.out.println("注册成功!");
} else {
System.out.println("注册失败!");
}
} else {
System.out.println("两次输入的密码不同!");
}
}
//注册方法
public boolean register(String username,String password) throws SQLException {
boolean a=false;
if(username == null || password == null) {
return a;
}
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try {
conn = JDBCUtils.getConnection();
//定义sql
String sql1 = "select * from a1 where name = '"+username+"'";
//获取执行sql对象
stmt = conn.createStatement();
//执行查询
rs=stmt.executeQuery(sql1);
//定义sql
String sql = "INSERT INTO a1 (name,password) VALUES ('"+username+"','"+password+"')";
//获取执行sql对象
stmt = conn.createStatement();
//执行插入
if(stmt.executeUpdate(sql)>=1)
a=true;
return a;
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.close(stmt,conn);
JDBCUtils.close(rs,stmt,conn);
}
return a;
}
//登陆方法
public boolean login(String username,String password) throws SQLException {
if(username==null||password==null){
return false;
}
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
conn = JDBCUtils.getConnection();
//定义sql
String sql ="select * from a1 where name = '"+username+"' and password = '"+password+"'";
//获取执行sql对象
stmt = conn.createStatement();
//执行查询
rs = stmt.executeQuery(sql);
return rs.next();
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.close(rs,stmt,conn);
}
return false;
}
}
jdbc.properties
url=jdbc:mysql://localhost:3306/a?useSSL=false
user=root
password=123456
driver=com.mysql.jdbc.Driver
JDBCtils
//读取文件,只需读取一次即可拿到这些值,使用静态代码块
static {
try {
//1.创建Properties集合类
Properties pro=new Properties();
//获取src路径下文件————>ClassLoader 类加载器
ClassLoader classLoader=JDBCUtils.class.getClassLoader();
URL res=classLoader.getResource("jdbc.properties");
String path = res.getPath();
System.out.println(path);
//2.加载文件
pro.load(new FileReader(path));
//3.获取数据,赋值
url = pro.getProperty("url");
user = pro.getProperty("user");
psssword = pro.getProperty("password");
driver = pro.getProperty("driver");
//4.注册驱动
Class.forName(driver);
}catch (IOException e){
e.printStackTrace();
}catch (ClassNotFoundException e1){
e1.printStackTrace();
}
}
//连接
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url,user,psssword);
}
//释放资源
public static void close(ResultSet rs,Statement stmt,Connection conn) throws SQLException{ //ResultSet 结果集
if(rs != null){
rs.close();
stmt.close();
}
if (stmt != null){
stmt.close();
}
if(conn != null){
conn.close();
}
}
public static void close(Statement stmt,Connection conn) throws SQLException{
if (stmt != null){
stmt.close();
}
if(conn != null){
conn.close();
}
}
}
运行结果
回滚
会将运行过程中的错误代码列出,并且不会再执行。