操作数据库之前需要一个jar包
链接:https://pan.baidu.com/s/1i0kPfFevmZhO1X74_XHgpw 提取码: 8zrq
在你的工程下新建一个名叫lib的文件夹,放入jar包
然后右键点击jar包Build Path->Add to Build to path,完成了的效果是这样
接下来就可以写代码了
对数据库的插入操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class AddDemo {
public static void main(String[] args) {
final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
final String DB_URL = "jdbc:mysql://localhost:3306/hotel";//最后面的hotel为数据库名
try {
// 加载驱动
Class.forName(JDBC_DRIVER);
// 连接数据库,第一个参数为url,第二个第三个分别为用户名和密码
Connection conn = DriverManager.getConnection(DB_URL, "root", "root");
// 获取statement对象(容器)
Statement sta = conn.createStatement();
// sql语句
String sql = "INSERT INTO USER (userId,userName,sex,tel,PASSWORD,email)VALUES(201,'熊根滔java','1','953887764','123456','123@qq.com')";
//re为影响的行数
int re = sta.executeUpdate(sql);
if (re == 1) {
System.out.println("增加成功");
} else {
System.out.println("增加失败");
}
//关闭流,根据使用顺序来倒序关闭流(最开始用的connection,然后statement,关闭顺序则反过来)
sta.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
对数据库的删除操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class DeleteDemo {
public static void main(String[] args) {
final String JDBC_DRIVER ="com.mysql.jdbc.Driver";
final String DB_URL = "jdbc:mysql://localhost:3306/hotel";
System.out.println("输入一个id:");
Scanner scanner = new Scanner(System.in);
int id =scanner.nextInt();
try {
Class.forName(JDBC_DRIVER);
Connection conn = DriverManager.getConnection(DB_URL,"root","root");
Statement sta = conn.createStatement();
//使用字符串拼接的方式来构造sql语句
String sql ="DELETE FROM USER WHERE userId = " +id;
//受影响的行数,如果大于0肯定删除成功了
int re = sta.executeUpdate(sql);
if (re > 0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败或id不存在");
}
//关闭流
sta.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
对数据库的更新操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import com.zyk.utils_jdbc.MyDBUtils;
public class UpdateDemo {
public static void main(String[] args) {
final String JDBC_DRIVER ="com.mysql.jdbc.Driver";
final String DB_URL = "jdbc:mysql://localhost:3306/hotel";
try {
Class.forName(JDBC_DRIVER);
Connection conn = DriverManager.getConnection(DB_URL,"root","root");
Statement sta = conn.createStatement();
String sql = "UPDATE USER SET userName = '新改的id' WHERE userId = 5";
int re = sta.executeUpdate(sql);
if (re == 1) {
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
对数据库的查询操作(查询所有)
数据库的查询操作和其他三个不一样的地方就是:其他三个statement.executeQuery(sql)返回的是受影响的行数,它返回的是一个结果集resultSet,需要对resultset进行遍历操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SelectDemo {
public static void main(String[] args) {
try {
//指定连接类型
Class.forName("com.mysql.jdbc.Driver");
//获取连接
Connection connection =DriverManager.getConnection("jdbc:mysql://localhost:3306/hotel","root","root");
//创建statement对象
Statement statement = connection.createStatement();
//创建sql语句
String sql ="select * from user";
//返回的结果集合
ResultSet resultSet = statement.executeQuery(sql);
//循环条件:是否有值
while(resultSet.next()) {
//根据数据库中的字段名来获取值,如果返回的int类型的就getInt("字段名"),如果是字符串类型的就getString(字段名)
System.out.println(resultSet.getInt("userId") + "---"+resultSet.getString("userName"));
}
//关闭流
resultSet.close();
statement.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}