【例17.3】找出所有姓张的同学
本例在例17.2的基础上修改,为查询语句添加like关键字,然后将姓张的同学的全部信息输出到控制台中。
SQL脚本
/*
Navicat MySQL Data Transfer
Source Server : 8.0
Source Server Version : 80022
Source Host : localhost:3307
Source Database : test
Target Server Type : MYSQL
Target Server Version : 80022
File Encoding : 65001
Date: 2021-02-19 14:05:38
*/
CREATE DATABASE test;
USE test;
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb_stu`
-- ----------------------------
DROP TABLE IF EXISTS `tb_stu`;
CREATE TABLE `tb_stu` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of tb_stu
-- ----------------------------
INSERT INTO `tb_stu` VALUES ('1', '张三', '男', '1998-02-06');
INSERT INTO `tb_stu` VALUES ('2', '李四', '女', '1995-06-28');
INSERT INTO `tb_stu` VALUES ('3', '王五', '女', '1999-11-23');
INSERT INTO `tb_stu` VALUES ('4', '赵六', '男', '2000-05-30');
commit;
Java中的代码
/*【例17.3】找出所有姓张的同学
* 本例在例17.2的基础上修改,为查询语句添加like关键字,
* 然后将姓张的同学的全部信息输出到控制台中。
* */
package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
public class JDBCDemo2 {
public static void main(String[] args) {
// TODO Auto-generated method stub
try{
Class.forName("com.mysql.cj.jdbc.Driver"); //加载数据库驱动类
}catch(ClassNotFoundException e) {
e.printStackTrace();
}
try {
Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&allowPublicKeyRetrieval=true","root","19971213");
Statement stmt = con.createStatement();
ResultSet res = stmt.executeQuery("select * from tb_stu where name like'张%'");
while(res.next()) { //如果当前语句不是最后一条,则进入循环,输出内容
String id = res.getString("id");
String name = res.getString("name");
String sex = res.getString("sex");
String birthday = res.getString("birthday");
System.out.print("编号:" + id);
System.out.print(" 姓名:" + name);
System.out.print(" 性别:" + sex);
System.out.println(" 生日:" + birthday);
}
con.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}