JDBC相关知识学习
JDBC : java数据库连接
JDBC:一套通用的java语言操作不同数据库的接口;
java连接数据库:
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库名称,登录名,密码
String url="jdbc:mysql://localhost:3306/lala";
String username="root";
String password="123456";
//创建连接
Connection connection = DriverManager.getConnection(url, username, password);
//获取操作对象
Statement statement = connection.createStatement();
//书写sql语句
String sql="insert into 表一(name,decl) values('战三','学生')";
//执行sql语句
statement.executeUpdate(sql);
//释放资源
connection.close();
statement.close();
DriverManager 驱动管理类
Connection 接口,与数据库建立连接
DML:查询表中数据 executeUpdate()
DQL: 增删改 executeQuery()
查询表中数据
ResultSet: 结果集对象,查询结果封装在结果集对象中
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/lala", "root", "123456");
Statement statement = con.createStatement();
String sql="select * from 表一";
ResultSet resultSet = statement.executeQuery(sql);
ArrayList<User> list = new ArrayList<>();
while(resultSet.next()){
int anInt = resultSet.getInt(1);
String username = resultSet.getString(2);
String decl = resultSet.getString(3);
User user = new User(username, decl);
list.add(user);
}
Iterator<User> iterator = list.iterator();
while(iterator.hasNext()){
User next = iterator.next();
String username = next.getUsername();
String decl = next.getDecl();
System.out.println(username+"==="+decl);
}
con.close();
statement.close();
resultSet.close();
查询语句:加入变量名
登录案例:
变量一:name 变量二: password
String sql =“ select * from 表名 where 列名= ‘ “+变量名1+” ’ and 列名=‘ ”+变量名+ “ ’ ”;
Scanner scanner = new Scanner(System.in);
System.out.println("请输入姓名:");
String username = scanner.nextLine();
System.out.println("请输入特征描述:");
String decl = scanner.nextLine();
Class.forName("com.mysql.jdbc.Driver");
//jdbc:mysql://localhost:3306/lala
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/lala","root","123456");
PreparedStatement statement = connection.prepareStatement("select * from 表一 where name=?and decl=?");
statement.setString(1,username);
statement.setString(2,decl);
ResultSet resultSet = statement.executeQuery();
if(resultSet.next()) {
System.out.println("登陆成功");}else{
System.out.println("登陆失败!!");
}
查询语句:预编译操作对象
prepareStatement=prepareStatement(“ select * from 表名 where 列名= ? and 列名=? ”)
注:此对象需要提前给出sql语句
参数使用?占位
给问号赋值:
prepareStatement.set参数类型(第几位问号,变量名)
ctrl alt +f 快速抽取成员变量
测试类
public class test {
public static void main(String[] args) throws Exception {
Connection connection = Util.getconnection();
PreparedStatement statement = connection.prepareStatement("select * from 表一");
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()){
int anInt = resultSet.getInt(1);
String username = resultSet.getString(2);
String decl = resultSet.getString(3);
User user = new User(username, decl);
System.out.println(anInt+"==="+user);
}
Util.close(connection,statement,resultSet);
}
}
连接类
public class Util {
private static String url;
private static String username;
private static String password;
public Util() {
}
static {
try {
Properties properties = new Properties();
properties.load(new FileReader("proterise.ptoterise"));
Class.forName("com.mysql.jdbc.Driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getconnection() throws Exception {
Connection connection = DriverManager.getConnection(url,username,password);
return connection;
}
public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) throws Exception {
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
}
public static void close(Connection connection, PreparedStatement statement) throws Exception {
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
}
}
批量插入数据库中
调用: addBatch()//添加批处理
executeBatch() //执行批处理
clearBatch() //清空批处理
批处理:
工具类:
public class Util {
private static String url;
private static String username;
private static String password;
public Util() {
}
static {
try {
Properties properties = new Properties();
properties.load(new FileReader("proterise.ptoterise"));
Class.forName("com.mysql.jdbc.Driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getconnection() throws Exception {
Connection connection = DriverManager.getConnection(url,username,password);
return connection;
}
public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) throws Exception {
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
}
public static void close(Connection connection, PreparedStatement statement) throws Exception {
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
}
}
用户信息类
public class User {
private int id;
private String username;
private String decl;
public int getId() {
return id;
}
@Override
public String toString() {
return "User{" +
"username='" + username + '\'' +
", decl='" + decl + '\'' +
'}';
}
public User(int id,String username, String decl) {
this.id=id;
this.username = username;
this.decl = decl;
}
public String getUsername() {
return username;
}
public String getDecl() {
return decl;
}
}
测试类
public class test2 {
public static void main(String[] args) throws Exception {
//批处理
Connection getconnection = Util.getconnection();
ArrayList<User> users = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
User lala = new User(i, "lala", "123456");
users.add(lala);
}
String sql="insert into 表一 values(?,?,?)";
PreparedStatement preparedStatement = getconnection.prepareStatement(sql);
for (User user : users) {
preparedStatement.setInt(1,user.getId());
preparedStatement.setString(2,user.getUsername());
preparedStatement.setString(3,user.getDecl());
preparedStatement.addBatch();//添加批处理
}
preparedStatement.executeBatch();//执行批处理
preparedStatement.clearBatch();//清空批处理
Util.close(getconnection,preparedStatement);
}
}
Java代码调用存储过程
1.获取能够调用存储过程,或者自定义函数的操作对象
prepareCall(sql);
2.sql语句书写
sql="{call <存储过程名称>[(参数一),(参数二),...]}";
一般参数用?占位;
输入参数赋值:
set参数类型(第几个问号,参数值)
输出参数:需要注册输出参数:
registerOutParameter(第几个问号,Types.参数类型)
3.调用execute() 执行
4.使用get方法获取结果
调用函数: select 函数名(参数一,参数二);
java调用自定义,内置方法:
1.获取能够调用存储过程,或者自定义函数的操作对象
prepareCall(sql);
2.sql语句书写
sql="{?=call <自定义方法名称>[(参数一),(参数二),...]}";
一般参数用?占位;
输入参数赋值:
set参数类型(第几个问号,参数值)
输出参数:需要注册输出参数:
registerOutParameter(第几个问号,Types.参数类型)
3.调用execute() 执行
4.使用get方法获取结果
获取自增长键值
1.首先在预编译声明中增加Statement.RETURN_GENERATED_KEYS参数变量,表示返回自增长键的值
prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
2.获取自增长值的结果值
getGeneratedKeys()//获取自增长键
案例:商品的买入,与订单的删除
//登录类
public class denglu {
public static int anInt;
public static boolean Dl(Connection connection) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入你的用户名:");
String name = scanner.nextLine();
System.out.println("请输入密码:");
String pass_word = scanner.nextLine();
String pass = md5.MD5(connection, pass_word);
PreparedStatement statement = connection.prepareStatement("select * from user where User_name=?and Pass_word=?");
statement.setString(1,name);
statement.setString(2,pass);
ResultSet resultSet = statement.executeQuery();
if(resultSet.next()){
anInt = resultSet.getInt(1);
return true;
}else{
return false;
}
}
//获取商品信息线程
public class infor extends Thread {
public Connection con;
public infor(Connection connection) {
this.con=connection;
}
@Override
public void run() {
try {
PreparedStatement statement = con.prepareStatement("select * from shopping");
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt(1); //获取商品编号
String name = resultSet.getString(2); //商品名称
int price = resultSet.getInt(3); //获取商品价格
int num= resultSet.getInt(4); //获取商品数量
if(num>0){
System.out.println("商品编号:"+id+"\t"+"商品名称:"+name+"\t"+"商品价格:"+price+"\t");
}else{
System.out.println("商品名称:"+name+" 货物卖完。"+"\t");
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//注册类
public class Login {
public Login() {
}
public static boolean zhuce( Connection connection) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入你的姓名:");
String name = scanner.nextLine();
System.out.println("请输入密码:");
String pass_word = scanner.nextLine();
String pass = md5.MD5(connection, pass_word);
PreparedStatement statement = connection.prepareStatement("insert into user(User_name,Pass_word) values(?,?)");
statement.setString(1,name);
statement.setString(2,pass);
int i = statement.executeUpdate();
if(i>0){
return true;
}else{
return false;
}
}
}
//密码加密类
public class md5 {
public md5() {
}
public static String MD5(Connection connection, String pass_word) throws Exception {
//sql="{?=call <自定义方法名称>[(参数一),(参数二),...]}";
//调用内置函数,进行密码加密;
String sql="{?=call md5(?)}";
CallableStatement call = connection.prepareCall(sql);
call.registerOutParameter(1, Types.VARCHAR);
call.setString(2,pass_word);
call.execute();
String string = call.getString(1);
call.close();
return string;
}
}
//菜单类
public class menu {
private static Connection con;
public static void menu1(Connection connection) throws Exception {
con=connection;
System.out.println("菜单项: 1.下订单 2.查看订单 3.删除订单 4.退出");
Scanner scanner = new Scanner(System.in);
int i = scanner.nextInt();
switch(i){
case 1:
add();
break;
case 2:
look();
break;
case 3:
dele();
break;
case 4:
con.close();
System.exit(0);
}
con.close();
}
private static void dele() throws SQLException {
PreparedStatement statement = con.prepareStatement("delete from connecting where connecting.`User_id`=?");
statement.setInt(1,denglu.anInt);
int i = statement.executeUpdate();
if(i>0){
System.out.println("删除成功!");
}else{
System.out.println("删除失败!");
}
}
private static void look() throws SQLException {
PreparedStatement statement = con.prepareStatement("select shopping.*,connecting.`count` from connecting,shopping where connecting.`Shopping_id`=shopping.`Shopping_id` and User_id=?");
statement.setInt(1,denglu.anInt);
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt(1); //获取商品编号
String name = resultSet.getString(2); //商品名称
int price = resultSet.getInt(3); //获取商品价格
int num= resultSet.getInt(4); //获取商品数量
int zs = resultSet.getInt(5);
int zongjia= zs*price;
System.out.println("商品编号:"+id+"\t"+"商品名称:"+name+"\t"+"商品价格:"+price+"\t"+"购买数量:"+zs+"\t"+"总价:"+zongjia);
}
}
//测试类
public class test {
public static void main(String[] args) throws Exception {
System.out.println("请选择: 1.注册 2.登录");
Connection connection = Util.getconnection();
Scanner scanner = new Scanner(System.in);
int i = scanner.nextInt();
switch(i){
case 1:
boolean zhuce = Login.zhuce(connection);
if(!zhuce){
break;
}
case 2:
System.out.println("请登录:");
boolean dl = denglu.Dl(connection);
if(dl){
System.out.println("登录成功");
menu.menu1(connection);
}else{
System.out.println("登录失败");
}
break;
}
}
}
//连接数据库工具类
public class Util {
private static String url;
private static String username;
private static String password;
public Util() {
}
static {
try {
Properties properties = new Properties();
properties.load(new FileReader("proterise.ptoterise"));
Class.forName("com.mysql.jdbc.Driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getconnection() throws Exception {
Connection connection = DriverManager.getConnection(url,username,password);
return connection;
}
public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) throws Exception {
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
}
public static void close(Connection connection, PreparedStatement statement) throws Exception {
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
}
}