mysql
一、创建数据库
//创建数据库
create database test;
//创建数据库,设置字符编码
create database test
//销毁数据库
drop database test;
二、创建表
创建表案例
// 创建年级表
create table grade(gradeid int primary key auto_increment,gradename varchar(20))default charset=utf8;
//创建成绩表
create table score(scoreid int primary key auto_increment,studentid int,courseid int ,scorenumber double,time datetime)default charset=utf8;
//创建学生表
create table student(studentid int primary key auto_increment,studentname varchar(10),password varchar(20),sex varchar(2),gradeid int ,phone varchar(15),address varchar(50),birthday datetime,email varchar(50))default charset=utf8;
//创建课程表
create teble course(courseid int primary key auto_increment,coursename varchar(20),studytime time,gradeid int)default charset=utf8;
//在表中添加新的字段
alter table grade add career int;
//修改表中字段名称
alter table student change gradeid career int;
三、CRUD操作语句
请移步:https://www.w3school.com.cn/sql/index.asp
四、JDBC
1、jdbc核心组件
2、jdbc连接步骤
3.执行sql语句 Statement
4.执行sql语句 PreparedStatement
PreparedStatement可以有效地防止sql注入式攻击。
5、反射处理结果集
public List<Student> getAllStudent(Class c) throws SQLException {
//1. 加载驱动
Connection connection = null;
PreparedStatement pps = null;
ResultSet resultSet = null;
List students = null;
Method[] declaredMethods=null;
String[] columnNames=null;
Object stu=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获得连接
String userName = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/test2?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3. 定义sql,创建预状态通道
String sql = "select * from student";
pps = connection.prepareStatement(sql);
// 执行sql
resultSet = pps.executeQuery();
students = new ArrayList<>();
//1. 得到数据库的查询结果的列信息
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
columnNames = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
columnNames[i] = metaData.getColumnName(i + 1);
System.out.println(columnNames[i]);
}
declaredMethods = c.getDeclaredMethods();
stu = c.newInstance();
} catch (Exception e) {
System.out.println("异常");
}
while (resultSet.next()) {
for (String columnName : columnNames) {
String methodName = "set" + columnName;
for (Method declaredMethod : declaredMethods) {
String name=declaredMethod.getName();
if (declaredMethod.getName().equalsIgnoreCase(methodName)) {
try {
stu = declaredMethod.invoke(stu, resultSet.getObject(methodName));
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
students.add(stu);
}
System.out.println("执行成功");
//5. 关闭资源
try {
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (Exception e) {
}
return students;
}
6、读取本地文件
InputStream inputStream = Student.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
dirverName = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("user");
password = properties.getProperty("password");
static{
//参数只写属性文件名即可,不需要写后缀
ResourceBundle bundle = ResourceBundle.getBundle("db");
driver = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("user");
password = bundle.getString("password");
}
7、DBCP C3P0 Druid数据库连接池
Druid的特点
连接进入连接池
/***
* 阿里的数据库连接池
* 性能最好的
* Druid
* */
public class DruidUtils {
//声明连接池对象
private static DruidDataSource ds;
static{
//实例化数据库连接池对象
ds=new DruidDataSource();
//实例化配置对象
Properties properties=new Properties();
try {
//加载配置文件内容
properties.load(DruidUtils.class.getResourceAsStream("dbcpconfig.properties"));
//设置驱动类全称
ds.setDriverClassName(properties.getProperty("driverClassName"));
//设置连接的数据库
ds.setUrl(properties.getProperty("url"));
//设置用户名
ds.setUsername(properties.getProperty("username"));
//设置密码
ds.setPassword(properties.getProperty("password"));
//设置最大连接数量
ds.setMaxActive(Integer.parseInt(properties.getProperty("maxActive")));
} catch (IOException e)
{ e.printStackTrace(); }
}
//获取连接对象
public static Connection getConnection() {
try {
return ds.getConnection();
} catch (SQLException e) { e.printStackTrace(); }
return null;
}
}
五、综合案例
1、航班信息管理系统
1.导入jar包,安装mysql,navicat,连接数据库;
2.在mysql中创建数据表,并插入数据;
3.在创建好的java项目中新建AirInfo类
public class AirInfo {
private int id;
private String airid;
private String address;
private String time;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAirid() {
return airid;
}
public void setAirid(String airid) {
this.airid = airid;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
}
4.创建连接数据库和执行相关操作的工具类DBUtil类
public class DBUtil {
private Connection connection;
private PreparedStatement pps;
private ResultSet resultSet;
private int count;
private static String username;
private static String password;
private static String url;
private static String driver;
static {
try{
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//mysql5 为com.mysql.jdbc.Driver,mysql8为com.mysql.cj.jdbc.Driver
username = "root";
password = "123456";
//密码是安装数据库时自己设置
url = "jdbc:mysql://localhost:3306/airinfo?serverTimezone=UTC";
//其中airinfo是数据库名称,其他为固定格式
}catch(Exception e){
e.printStackTrace();
}
}
//1.获取连接
protected Connection getConnection(){
try {
connection= DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//2. 预处理sql代码,开启预状态通道pps
protected PreparedStatement getPps(String sql){
try {
pps= getConnection().prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
//3. 设置占位符内容
protected void setParam(List list){
if(list!=null&&list.size()>0){
for (int i = 0; i <list.size() ; i++) {
try {
pps.setObject(i+1,list.get(i));
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//4. 获取sql返回集
protected ResultSet getQuery(String sql,List list){
getPps(sql);
setParam(list);
try {
resultSet=pps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
protected ResultSet getQuery(String sql){
getPps(sql);
try {
resultSet=pps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
//5. 获取返回集的内容
protected List<AirInfo> getResultSet(ResultSet resultSet){
List<AirInfo> airInfos=new ArrayList<>();
try {
while (resultSet.next()) {
AirInfo airInfo=new AirInfo();
airInfo.setId(resultSet.getInt("id"));
airInfo.setAirid(resultSet.getString("airid"));
airInfo.setAddress(resultSet.getString("address"));
airInfo.setTime(resultSet.getString("time"));
airInfos.add(airInfo);
}
}catch (Exception e){
e.printStackTrace();
}
return airInfos;
}
//6. 更新数据库,返回受影响条数
protected int getUpdateCount(String sql,List list){
getPps(sql);
setParam(list);
try {
count=pps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
//7. 关闭所有资源
protected void closeAll(){
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(pps!=null){
try {
pps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
5.创建接口AirInfoDao
public interface AirInfoDao {
//1. 列出所有的航班信息
public void show();
//2. 按起飞时间查询
public void findByTime(String time);
//3. 按目的地查询
public void findByAddress(String address);
//4. 删除航班
public void deleteAirInfoById(int id);
//5. 更新航班
public void updateAirInfoById(int id);
//6. 退出系统
public void closeAll();
//7. 通过id查找
public boolean findById(int id);
}
6.创建接口类的实现类AirInfoDaoImpl类
public class AirInfoDaoImpl extends DBUtil implements AirInfoDao {
@Override
public void show() {
String sql="select *from air";
ResultSet query = getQuery(sql);
List<AirInfo> list = new ArrayList<>();
list = getResultSet(query);
if(list.size()>0&&list!=null) {
System.out.println("编号 航班号 目的地 起飞时间");
for (AirInfo airInfo : list) {
System.out.println(airInfo.getId() + " " + airInfo.getAirid() + " " + airInfo.getAddress() + " " + airInfo.getTime());
}
}else{
System.out.println("没有航班信息!");
}
}
@Override
public void findByTime(String time) {
String sql="select *from air where time=?";
List list=new ArrayList();
list.add(time);
ResultSet query = getQuery(sql,list);
List<AirInfo> list1=getResultSet(query);
if(list1.size()>0&&list1!=null) {
System.out.println("编号 航班号 目的地 起飞时间");
for (AirInfo airInfo : list1) {
System.out.println(airInfo.getId() + " " + airInfo.getAirid() + " " + airInfo.getAddress() + " " + airInfo.getTime());
}
}else{
System.out.println("没有出发时间为:"+time+"的航班信息!");
}
}
@Override
public void findByAddress(String address) {
String sql="select *from air where address=?";
List list=new ArrayList();
list.add(address);
ResultSet query = getQuery(sql,list);
List<AirInfo> list1=getResultSet(query);
if(list1.size()>0&&list1!=null) {
System.out.println("编号 航班号 目的地 起飞时间");
for (AirInfo airInfo : list1) {
System.out.println(airInfo.getId() + " " + airInfo.getAirid() + " " + airInfo.getAddress() + " " + airInfo.getTime());
}
}else{
System.out.println("没有目的地为:"+address+"的航班信息!");
}
}
@Override
public void deleteAirInfoById(int id) {
if(findById(id)) {
String sql = "delete from air where id=?";
List list = new ArrayList();
list.add(id);
int num=getUpdateCount(sql,list);
if(num==1){
System.out.println("删除成功!");
}else{
System.out.println("删除失败!");
}
}
}
@Override
public void updateAirInfoById(int id) {
Scanner sc=new Scanner(System.in);
String airid="";
String address="";
String time="";
if(findById(id)){
System.out.println("请输入新的航班号:");
airid=sc.next();
System.out.println("请输入新的目的地:");
address=sc.next();
System.out.println("请输入新的出发时间:");
time=sc.next();
String sql="update air set id="+id+",airid=?,address=?,time=? where id="+id;
List<String> list=new ArrayList<>();
list.add(airid);
list.add(address);
list.add(time);
int num=getUpdateCount(sql,list);
if(num==1){
System.out.println("更新成功!");
}else{
System.out.println("更新失败!");
}
}
}
@Override
public void closeAll() {
closeAll();
}
@Override
public boolean findById(int id) {
String sql="select * from air where id=?";
List list=new ArrayList();
list.add(id);
ResultSet query = getQuery(sql,list);
List<AirInfo> list1=getResultSet(query);
if(list1.size()>0&&list1!=null) {
System.out.println("编号 航班号 目的地 起飞时间");
for (AirInfo airInfo : list1) {
System.out.println(airInfo.getId() + " " + airInfo.getAirid() + " " + airInfo.getAddress() + " " + airInfo.getTime());
}
return true;
}else{
System.out.println("没有id为:"+id+"的航班信息!");
return false;
}
}
}
7.创建主方法调用实现对航班信息的管理
public class Mian {
public static void main(String[] args) {
AirInfoDaoImpl airInfoDao=new AirInfoDaoImpl();
Scanner sc=new Scanner(System.in);
System.out.println("******************************欢迎使用航班信息管理系统******************************");
int choose=0;
while(true&&choose!=6){
System.out.println("请选择操作:1.列出所有航班 2.按起飞时间查询 3.按目的地查询 4.删除航班 5.更新航班 6.退出系统");
choose=sc.nextInt();
switch(choose){
case 1:
airInfoDao.show();
break;
case 2:
System.out.println("请输入时间:");
String time=sc.next();
airInfoDao.findByTime(time);
break;
case 3:
System.out.println("请输入目的地:");
String address=sc.next();
airInfoDao.findByAddress(address);
break;
case 4:
System.out.println("请输入需要删除的id:");
int id=sc.nextInt();
airInfoDao.deleteAirInfoById(id);
break;
case 5:
System.out.println("请输入需要更新的航班信息:");
int id1 =sc.nextInt();
airInfoDao.updateAirInfoById(id1);
break;
case 6:
break;
default:
System.out.println("指令错误");
break;
}
}
System.out.println("已退出系统!");
}
}