---恢复内容开始---
我要评论create table `account` (
`id` int (11),
`name` char (60),
`balance` int (11)
);
insert into `account` (`id`, `name`, `balance`) values('1','aa','2000');
insert into `account` (`id`, `name`, `balance`) values('2','bb','2000');
insert into `account` (`id`, `name`, `balance`) values('3','王五','2000');
1.用jdbc设置aa的balance值为1500
public class jdbcdemo1 {
public static void main(string[] args) throws exception {
//1. 导入驱动jar包
//2.注册驱动
class.forname("com.mysql.jdbc.driver");
//3.获取数据库连接对象
connection conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
//4.定义sql语句
string sql = "update account set balance = 1500 where id = 1";
//5.获取执行sql的对象 statement
statement stmt = conn.createstatement();
//6.执行sql
int count = stmt.executeupdate(sql);
//7.处理结果
system.out.println(count);
//8.释放资源
stmt.close();
conn.close();
}
}
2.用jdbc添加姓名cc,balance为3000
public class jdbcdemo2 {
public static void main(string[] args) {
statement stmt = null;
connection conn = null;
try {
//1. 注册驱动
class.forname("com.mysql.jdbc.driver");
//2. 定义sql
string sql = "insert into account values(null,'cc',3000)";
//3.获取connection对象
conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
//4.获取执行sql的对象 statement
stmt = conn.createstatement();
//5.执行sql
int count = stmt.executeupdate(sql);//影响的行数
//6.处理结果
system.out.println(count);
if(count > 0){
system.out.println("添加成功！");
}else{
system.out.println("添加失败！");
}
} catch (classnotfoundexception e) {
e.printstacktrace();
} catch (sqlexception e) {
e.printstacktrace();
}finally {
//stmt.close();
//7. 释放资源
//避免空指针异常
if(stmt != null){
try {
stmt.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if(conn != null){
try {
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
}
}
3.用jdbc删除id为3的数据
public class jdbcdemo3 {
public static void main(string[] args) {
connection conn = null;
statement stmt = null;
try {
//1. 注册驱动
class.forname("com.mysql.jdbc.driver");
//2.获取连接对象
conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
//conn = jdbcutils.getconnection("jdbc:mysql:///db3", "root", "root");
//3.定义sql
string sql = "delete from account where id = 3";
//4.获取执行sql对象
stmt = conn.createstatement();
//5.执行sql
int count = stmt.executeupdate(sql);
//6.处理结果
system.out.println(count);
if(count > 0){
system.out.println("删除成功！");
}else{
system.out.println("删除失败");
}
} catch (classnotfoundexception e) {
e.printstacktrace();
} catch (sqlexception e) {
e.printstacktrace();
} finally {
//7.释放资源
if(stmt != null){
try {
stmt.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if(conn != null){
try {
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
}
}
4.用jdbc创建一个student表
/**
* 执行ddl语句
*/
public class jdbcdemo5 {
public static void main(string[] args) {
connection conn = null;
statement stmt = null;
try {
//1. 注册驱动
class.forname("com.mysql.jdbc.driver");
//2.获取连接对象
conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
//3.定义sql
string sql = "create table student1 (id int , name varchar(20))";
//4.获取执行sql对象
stmt = conn.createstatement();
//5.执行sql
int count = stmt.executeupdate(sql);
//6.处理结果
system.out.println(count);
} catch (classnotfoundexception e) {
e.printstacktrace();
} catch (sqlexception e) {
e.printstacktrace();
} finally {
//7.释放资源
if(stmt != null){
try {
stmt.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if(conn != null){
try {
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
}
}
5.用jdbc查询account表中所有数据
/**
* 执行ddl语句
*/
public class jdbcdemo7 {
public static void main(string[] args) {
connection conn = null;
statement stmt = null;
resultset rs = null;
try {
//1. 注册驱动
class.forname("com.mysql.jdbc.driver");
//2.获取连接对象
conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
//3.定义sql
string sql = "select * from account";
//4.获取执行sql对象
stmt = conn.createstatement();
//5.执行sql
rs = stmt.executequery(sql);
//6.处理结果
//循环判断游标是否是最后一行末尾。
while(rs.next()){
//获取数据
//6.2 获取数据
int id = rs.getint(1);
string name = rs.getstring("name");
double balance = rs.getdouble(3);
system.out.println(id + "---" + name + "---" + balance);
}
/* //6.1 让游标向下移动一行
if(rs.next()){
//判断是否有数据
//6.2 获取数据
int id = rs.getint(1);
string name = rs.getstring("name");
double balance = rs.getdouble(3);
system.out.println(id + "---" + name + "---" + balance);
}
//6.1 让游标向下移动一行
if(rs.next()){
//判断是否有数据
//6.2 获取数据
int id = rs.getint(1);
string name = rs.getstring("name");
double balance = rs.getdouble(3);
system.out.println(id + "---" + name + "---" + balance);
}
//6.1 让游标向下移动一行
if(rs.next()){
//判断是否有数据
//6.2 获取数据
int id = rs.getint(1);
string name = rs.getstring("name");
double balance = rs.getdouble(3);
system.out.println(id + "---" + name + "---" + balance);
}
//6.1 让游标向下移动一行
if(rs.next()){
//判断是否有数据
//6.2 获取数据
int id = rs.getint(1);
string name = rs.getstring("name");
double balance = rs.getdouble(3);
system.out.println(id + "---" + name + "---" + balance);
}*/
/* //6.1 让游标向下移动一行
rs.next();
//6.2 获取数据
int id2 = rs.getint(1);
string name2 = rs.getstring("name");
double balance2 = rs.getdouble(3);
system.out.println(id2 + "---" + name2 + "---" + balance2);
//6.1 让游标向下移动一行
rs.next();
//6.2 获取数据
int id3 = rs.getint(1);
string name3 = rs.getstring("name");
double balance3 = rs.getdouble(3);
system.out.println(id3 + "---" + name3 + "---" + balance3);*/
} catch (classnotfoundexception e) {
e.printstacktrace();
} catch (sqlexception e) {
e.printstacktrace();
} finally {
//7.释放资源
if(rs != null){
try {
rs.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if(conn != null){
try {
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
}
}
6.用jdbc查询account表中所有数据
public class emp {
private int id;
private string ename;
private int job_id;
private int mgr;
private date joindate;
private double salary;
private double bonus;
private int dept_id;
public int getid() {
return id;
}
public void setid(int id) {
this.id = id;
}
public string getename() {
return ename;
}
public void setename(string ename) {
this.ename = ename;
}
public int getjob_id() {
return job_id;
}
public void setjob_id(int job_id) {
this.job_id = job_id;
}
public int getmgr() {
return mgr;
}
public void setmgr(int mgr) {
this.mgr = mgr;
}
public date getjoindate() {
return joindate;
}
public void setjoindate(date joindate) {
this.joindate = joindate;
}
public double getsalary() {
return salary;
}
public void setsalary(double salary) {
this.salary = salary;
}
public int getdept_id() {
return dept_id;
}
public void setdept_id(int dept_id) {
this.dept_id = dept_id;
}
public double getbonus() {
return bonus;
}
public void setbonus(double bonus) {
this.bonus = bonus;
}
@override
public string tostring() {
return "emp{" +
"id=" + id +
", ename='" + ename + '\'' +
", job_id=" + job_id +
", mgr=" + mgr +
", joindate=" + joindate +
", salary=" + salary +
", bonus=" + bonus +
", dept_id=" + dept_id +
'}';
}
}
public class jdbcdemo8 {
public static void main(string[] args) {
list<emp> list = new jdbcdemo8().findall();
system.out.println(list);
system.out.println(list.size());
}
/**
* 查询所有emp对象
* @return
*/
public list<emp> findall(){
connection conn = null;
statement stmt = null;
resultset rs = null;
list<emp> list = null;
try {
//1.注册驱动
class.forname("com.mysql.jdbc.driver");
//2.获取连接
conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
//3.定义sql
string sql = "select * from emp";
//4.获取执行sql的对象
stmt = conn.createstatement();
//5.执行sql
rs = stmt.executequery(sql);
//6.遍历结果集，封装对象，装载集合
emp emp = null;
list = new arraylist<emp>();
while(rs.next()){
//获取数据
int id = rs.getint("id");
string ename = rs.getstring("name");
string ename = rs.getstring("");
date joindate = rs.getdate("joindate");
double salary = rs.getdouble("salary");
int dept_id = rs.getint("dept_id");
// 创建emp对象,并赋值
emp = new emp();
emp.setid(id);
emp.setename(ename);
emp.setjob_id(job_id);
emp.setmgr(mgr);
emp.setjoindate(joindate);
emp.setsalary(salary);
emp.setbonus(bonus);
emp.setdept_id(dept_id);
//装载集合
list.add(emp);
}
} catch (classnotfoundexception e) {
e.printstacktrace();
} catch (sqlexception e) {
e.printstacktrace();
}finally {
if(rs != null){
try {
rs.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if(conn != null){
try {
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
return list;
}
/**
* 演示jdbc工具类
* @return
*/
public list<emp> findall2(){
connection conn = null;
statement stmt = null;
resultset rs = null;
list<emp> list = null;
try {
/* //1.注册驱动
class.forname("com.mysql.jdbc.driver");
//2.获取连接
conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");*/
conn = jdbcutils.getconnection();
//3.定义sql
string sql = "select * from emp";
//4.获取执行sql的对象
stmt = conn.createstatement();
//5.执行sql
rs = stmt.executequery(sql);
//6.遍历结果集，封装对象，装载集合
emp emp = null;
list = new arraylist<emp>();
while(rs.next()){
//获取数据
int id = rs.getint("id");
string ename = rs.getstring("ename");
int job_id = rs.getint("job_id");
int mgr = rs.getint("mgr");
date joindate = rs.getdate("joindate");
double salary = rs.getdouble("salary");
double bonus = rs.getdouble("bonus");
int dept_id = rs.getint("dept_id");
// 创建emp对象,并赋值
emp = new emp();
emp.setid(id);
emp.setename(ename);
emp.setjob_id(job_id);
emp.setmgr(mgr);
emp.setjoindate(joindate);
emp.setsalary(salary);
emp.setbonus(bonus);
emp.setdept_id(dept_id);
//装载集合
list.add(emp);
}
} catch (sqlexception e) {
e.printstacktrace();
}finally {
/*if(rs != null){
try {
rs.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if(conn != null){
try {
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}*/
jdbcutils.close(rs,stmt,conn);
}
return list;
}
}
7.jdbc工具类
/**
* jdbc工具类
*/
public class jdbcutils {
private static string url;
private static string user;
private static string password;
private static string driver;
/**
* 文件的读取，只需要读取一次即可拿到这些值。使用静态代码块
*/
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);///d:/ideaprojects/itcast/out/production/day04_jdbc/jdbc.properties
//2. 加载文件
// pro.load(new filereader("d:\\ideaprojects\\itcast\\day04_jdbc\\src\\jdbc.properties"));
pro.load(new filereader(path));
//3. 获取数据，赋值
url = pro.getproperty("url");
user = pro.getproperty("user");
password = pro.getproperty("password");
driver = pro.getproperty("driver");
//4. 注册驱动
class.forname(driver);
} catch (ioexception e) {
e.printstacktrace();
} catch (classnotfoundexception e) {
e.printstacktrace();
}
}
/**
* 获取连接
* @return 连接对象
*/
public static connection getconnection() throws sqlexception {
return drivermanager.getconnection(url, user, password);
}
/**
* 释放资源
* @param stmt
* @param conn
*/
public static void close(statement stmt,connection conn){
if( stmt != null){
try {
stmt.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if( conn != null){
try {
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
/**
* 释放资源
* @param stmt
* @param conn
*/
public static void close(resultset rs,statement stmt, connection conn){
if( rs != null){
try {
rs.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if( stmt != null){
try {
stmt.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if( conn != null){
try {
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
}
8.通过键盘录入用户名和密码，判断用户是否登录成功
/**
* 练习：
* * 需求：
* 1. 通过键盘录入用户名和密码
* 2. 判断用户是否登录成功
*/
public class jdbcdemo9 {
public static void main(string[] args) {
//1.键盘录入，接受用户名和密码
scanner sc = new scanner(system.in);
system.out.println("请输入用户名：");
string username = sc.nextline();
system.out.println("请输入密码：");
string password = sc.nextline();
//2.调用方法
boolean flag = new jdbcdemo9().login2(username, password);
//3.判断结果，输出不同语句
if(flag){
//登录成功
system.out.println("登录成功！");
}else{
system.out.println("用户名或密码错误！");
}
}
/**
* 登录方法
*/
public boolean login(string username ,string password){
if(username == null || password == null){
return false;
}
//连接数据库判断是否登录成功
connection conn = null;
statement stmt = null;
resultset rs = null;
//1.获取连接
try {
conn = jdbcutils.getconnection();
//2.定义sql
string sql = "select * from user where username = '"+username+"' and password = '"+password+"' ";
system.out.println(sql);
//3.获取执行sql的对象
stmt = conn.createstatement();
//4.执行查询
rs = stmt.executequery(sql);
//5.判断
/* if(rs.next()){//如果有下一行，则返回true
return true;
}else{
return false;
}*/
return rs.next();//如果有下一行，则返回true
} catch (sqlexception e) {
e.printstacktrace();
}finally {
jdbcutils.close(rs,stmt,conn);
}
return false;
}
/**
* 登录方法,使用preparedstatement实现
*/
public boolean login2(string username ,string password){
if(username == null || password == null){
return false;
}
//连接数据库判断是否登录成功
connection conn = null;
preparedstatement pstmt = null;
resultset rs = null;
//1.获取连接
try {
conn = jdbcutils.getconnection();
//2.定义sql
string sql = "select * from user where username = ? and password = ?";
//3.获取执行sql的对象
pstmt = conn.preparestatement(sql);
//给?赋值
pstmt.setstring(1,username);
pstmt.setstring(2,password);
//4.执行查询,不需要传递sql
rs = pstmt.executequery();
//5.判断
/* if(rs.next()){//如果有下一行，则返回true
return true;
}else{
return false;
}*/
return rs.next();//如果有下一行，则返回true
} catch (sqlexception e) {
e.printstacktrace();
}finally {
jdbcutils.close(rs,pstmt,conn);
}
return false;
}
}
9.jdbc事务操作
/**
* 事务操作
*/
public class jdbcdemo10 {
public static void main(string[] args) {
connection conn = null;
preparedstatement pstmt1 = null;
preparedstatement pstmt2 = null;
try {
//1.获取连接
conn = jdbcutils.getconnection();
//开启事务
conn.setautocommit(false);
//2.定义sql
//2.1 张三 - 500
string sql1 = "update account set balance = balance - ? where id = ?";
//2.2 李四 + 500
string sql2 = "update account set balance = balance + ? where id = ?";
//3.获取执行sql对象
pstmt1 = conn.preparestatement(sql1);
pstmt2 = conn.preparestatement(sql2);
//4. 设置参数
pstmt1.setdouble(1,500);
pstmt1.setint(2,1);
pstmt2.setdouble(1,500);
pstmt2.setint(2,2);
//5.执行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,null);
}
}
}
---恢复内容结束---
如对本文有疑问， 点击进行留言回复！！
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火，普罗米修斯的崛起
网友评论