学习做网站的网站,关键词排名哪里查,沪深300指数,镇江网站建设工作室欢迎关注公众号#xff1a;xfxuezhangMySQL数据库与JDBC编程JDBC (Java Database Connectivity)DDL(Data Definition Language#xff0c;数据定义语言)语句创建表修改表结构增加列定义修改列定义删除列删除数据表清空表MySQL重命名数据表MySQL修改列名数据库约束索引(一个数…欢迎关注公众号xfxuezhangMySQL数据库与JDBC编程JDBC (Java Database Connectivity)DDL(Data Definition Language数据定义语言)语句创建表修改表结构增加列定义修改列定义删除列删除数据表清空表MySQL重命名数据表MySQL修改列名数据库约束索引(一个数据库对象)视图DML(Data Definition Language数据定义语言)语句insert into语句update语句delete from语句单表查询where后的其他运算符order by输出排序数据库函数MySQL单行函数分组和组函数group by分组多表连接查询交叉连接自然连接using子句连接on子句连接左、右、全外连接子查询集合运算union并运算minus差运算intersect交运算JDBC的经典用法JDBC编程步骤示例简单SQL查询执行SQL语句的方法使用executeLargeUpdate方法执行DDL和DML语句示例读取ini文件连接并创建数据表使用PreparedStatement执行SQL语句示例使用PreparedStatement插入记录使用CallableStatement调用存储过程示例调用存储功能管理结果集可滚动、可更改的结果集示例创建可滚动、可更改的结果集处理Blob类型数据示例通过SQL的Blob存储并读取图片数据使用ResultSetMetaData分析结果集示例分析结果集使用RowSet包装结果集RowSetFactory和RowSet示例通过RowSetFactory使用jdbcRowSet离线RowSet示例CachedRowSet离线操作SQL事务处理事务的概念JDBC事务支持使用批量更新MySQL数据库与JDBC编程JDBC (Java Database Connectivity)对于关系数据库而言最基本的数据存储单元是数据表。SQLStructured Query Language结构化查询语言。DDL(Data Definition Language数据定义语言)语句主要操作数据库对象。创建表CREATE TABLE [模式名.] 表名(columnName1 datatype [default expr],...)利用子查询建表CREATE TABLE [模式名.] 表名 [col[, col]]asselect * from user_inf;修改表结构增加列定义ALTER TABLE 表名add(columnName1 datatype [default expr],...);字符串值由单引号引起。修改列定义ALTER TABLE 表名modify columnName datatype [default expr] [first|afterColName];first、afterColName指定需要将目标修改到指定位置。删除列ALTER TABLE 表名drop columnName;删除数据表DROP TABLE 表名;表结构删除表对象不再存在表的所有数据被删除该表所有相关的索引、约束也被删除。清空表TRUNCATE 表名;删除表内的全部数据但保留表结构。MySQL重命名数据表ALTER TABLE 表名 rename to 新表名;MySQL修改列名ALTER TABLE 表名 CHANGE 列名 新列名 type [default expr] [first|afterColName]数据库约束5种完整性约束1、NOT NULL非空约束指定某列不能为空。CREATE TABLE 表名 (id int NOT NULL);2、UNIQUE唯一约束指定某列或者某几列组合不能重复。1、列级约束name VARCHAR(255) UNIQUE;2、表级约束(为多列组合建立唯一约束或想自行指定约束名)CREATE TABLE 表名(# 表级约束语法建立唯一约束UNIQUE (name),# 并指定约束名CONSTRAINT 新约束名 UNIQUE(pass),# 指定两列组合不允许重复CONSTRAINT 新约束名 UNIQUE(name, pass))3、MySQL删除约束ALTER TABLE 表名 DROP INDEX 约束名;4、大部分数据库删除约束ALTER TABLE 表名 DROP CONSTRAINT 约束名;3、PRIMARY KEY主键指定该列的值可以唯一地标识该条记录。1、列级约束CREATE TABLE 表名 (id INT PRIMARY KEY);2、表级约束CREATE TABLE 表名 (id INT,CONSTRAINT 约束名 PRIMARY KEY(id));3、建立多列组合的主键约束CREATE TABLE 表名 (id INT,name VARCHAR(255),PRIMARY KEY(id, name));4、删除主键约束ALTER TABLE 表名 DROP PRIMARY KEY;5、增加主键约束MODIFY采用列级约束语法ADD采用表级约束语法6、主键自增长id INT AUTO_INCREMENT PRIMARY KEY4、FOREIGN KEY外键指定该行记录从属于主表中的一条记录主要用于保证一个或两个数据表之间的参照完整性。1、指定两列的联合外键CREATE TABLE 表名(Sname VARCHAR(255),Spass VARCHAR(255),CONSTRAINT 约束名 FOREIGN KEY(Sname, Spass) REFERENCES 主表名(Fname, Fpass));2、级联删除(定义当删除主表记录时从表记录也会随之级联删除/从表记录的外键设置为null)CREATE TABLE 表名(Sname VARCHAR(255),FOREIGN KEY(Sname) REFERENCES 主表名(Fid) ON DELETE CASCADE # 也可用ON DELETE SET NULL);5、CHECK检查制定一个布尔表达式用于指定对应列的值必须满足该表达式。(MySQL不支持)CREATE TABLE 表名(key INT,CHECK(key10));索引(一个数据库对象)创建索引的唯一作用是加速对表的查询索引通过使用快速路径访问方法来快速定位数据从而减少了磁盘的I/0。创建索引1、自动当在表上定义主键约束、唯一约束、外键约束时系统自动创建对应的索引。2、手动CREATE INDEX 索引名 ON 表名(name, pass);删除索引1、自动数据表被删除时该表上的索引自动被删除。2、手动DROP INDEX 索引名 ON 表名;视图数据表中数据的逻辑显示。创建视图且不允许修改数据CREATE OR REPLACE VIEW 视图名 AS 查询语句 WITH CHECK OPTION;删除视图DROP VIEW 视图名;DML(Data Definition Language数据定义语言)语句主要操作数据表里的数据。由insert into、update、delete from三个命令组成。insert into语句向数据表中插入记录INSERT INTO 表名 (id, name, age) VALUES(1, sxf, null);带子查询的插入(要求所选的数据列个数相等、类型匹配)INSERT INTO 表名 (name) SELECT name FROM 表2;MySQL多条插入INSERT INTO 表名 (id, name) VALUES(1, a), (2,b);update语句修改数据表的记录。UPDATE 表名 SET namesxf, pass123 WHERE id1;delete from语句删除指定数据表的记录。总是整行删除。DELETE FROM 表名 WHERE id1 AND id5;单表查询字符串连接用concat()为数据列和表达式起别名用as或空格为表起别名用as或空格去除重复行用distinctSELECT DISTINCT CONCAT(na, me) AS myName FROM table t WHERE id*24;where后的其他运算符1、expr2expr1expr3expr1 BETWEEN expr2 AND expr3;2、expr1等于括号里的任意一个表达式的值expr1 IN(expr2, expr3, ...);3、字符串匹配下划线代表一个字符百分号代表任意多个字符like ‘_%’;4、是否为nullis null;order by输出排序desc降序默认asc升序。如果按多列排序则每列的asc、desc必须单独设定。SELECT * FROM table ORDER BY name DESC, id ASC;数据库函数多用在select和where后面。MySQL单行函数选出字符长度SELECT char_length(ip) FROM IP;计算sin值SELECT sin(1.57);为日期添加一定时间SELECT ADDDATE(1998-01-02, 3);获取当前日期SELECT CURDATE();获取当前时间SELECT curtime();如果expr1null返回expr2否则返回expr1ifnull(expr1, expr2)如果expr1expr2返回null否则返回expr1nullif(expr1, expr2)类似于三目运算符“:?”if(expr1, expr2, expr3)如果expr1为null返回true否则返回falseisnull(expr1)选择流程控制CASE valueWHEN value1 THEN result1WHEN value2 THEN result2...ELSE resultENDCASEWHEN condition1 THEN result1WHEN condition2 THEN result2...ELSE resultEND分组和组函数将一组记录作为整体计算返回一个结果。计算多行expr的平均值数据类型必须是数值型。distinct不计算重复值默认all计算重复值avg([distinct|all] expr)计算多行expr的总条数数据类型可以是任意类型。*表示统计该表内的记录行数distinct不计算重复值count({*|[distinct|all] expr})计算多行expr的最大值max(expr)计算多行expr的最小值min(expr)计算多行expr的综合sum([distinct|all] expr)group by分组对记录进行显示分组。相同的记录当成一组并过滤组havingSELECT * FROM ip GROUP BY ip HAVING count(*)2;多列的值完全相同时才会被当成一组SELECT count(*) FROM ip GROUP BY id, ip;多表连接查询交叉连接无需任何连接条件。SELECT s.*, name FROM studentTable s CROSS JOIN teacherTable t;自然连接以两个表中的同名列作为连接条件若没有同名列则效果等同交叉连接。SELECT s.*, name FROM studentTable s NATURAL JOIN teacherTable t;using子句连接显式指定两个表中的哪些同名列作为连接条件要求必须有同名列自然连接无法指定。SELECT s.*, name FROM studentTable s JOIN teacherTable t USING(id);on子句连接每个on子句只能指定一个连接条件。如果需要进行N表连接则需要N-1个join...on对。SELECT s.*, name FROM studentTable s JOIN teacherTable t ON s.teachert.id;左、右、全外连接分别使用left[outer]join、right[outer]join、full[outer]join连接条件通过on子句指定。左外连接把左边表中所有不满足连接条件的记录全部列出。右外连接把右边表中所有不满足连接条件的记录全部列出。全外连接(MySQL不支持)把两个表中所有不满足连接条件的记录全部列出。SELECT s.*, name FROM studentTable s LEFT JOIN teacherTable t ON s.teachert.id;子查询在查询语句中嵌套另一个查询支持多层嵌套。出现在from语句后当成数据表出现在where条件后作为过滤条件的值子查询要用括号括起来把子查询当成数据表时可以为该子查询起别名。SELECT * FROM (SELECT * FROM ip) t WHERE t.id1;SELECT * FROM ip WHERE id(SELECT Sid FROM Stable WHERE SnameSXF);集合运算两个结果集所包含的数据列的数量必须相等。两个结果集所包含的数据列的数据类型也必须一一对应。union并运算SELECT 语句 UNION SELECT 语句;minus差运算MySQL不支持使用not in代替。SELECT 语句 MINUS SELECT 语句;SELECT XX FROM XX WHERE (XX, XX) NOT IN (XX, XX);intersect交运算MySQL不支持使用join...on代替。SELECT 语句 INTERSECT SELECT 语句;SELECT XX FROM XX JOIN XX ON (XXXX) WHERE XXXX;JDBC的经典用法JDBC编程步骤Java 连接 MySQL 需要驱动包最新版下载地址为1、加载数据库驱动通常使用Class类的forName()静态方法来加载驱动。Class.forName(com.mysql.ch.hdbc.Driver);Class.forName(oracle.jdbc.driver.OracleDriver);2、通过DriverManager获取数据库连接DriverManager.getConnection(jdbc:mysql://hostname:port/databasename, user, pwd);3、通过Connection对象创建Statement对象createStatement()创建基本的Statement对象prepareStatement(String sql)根据传入的SQL语句创建预编译的Statement对象prepareCall(String sql)根据传入的SQL语句创建CallableStatement对象4、使用Statement执行SQL语句execute()可以执行任何SQL语句但比较麻烦executeUpdate()主要用于执行DML和DDL语句。执行DML语句返回受SQL语句影响的行数执行DDL语句返回0executeQuery()只能执行查询语句执行后返回代表查询结果的ResultSet对象5、操作结果集next()、previous()、first()、last()、beforeFirst()、afterLast()、absolute()等移动记录指针的方法getXxx()方法获取指针指向行、特定列的值。既可以使用列索引作为参数也可使用列名作为参数。6、回收数据库资源示例简单SQL查询package com.sxf.sql;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class Main {public static void main(String[] args) throws Exception{// 1、加载驱动使用反射知识。 Class.forName(com.mysql.cj.jdbc.Driver);try {// 2、使用DriverManager获取数据库连接。 Connection conn DriverManager.getConnection(jdbc:mysql://127.0.0.1:3306/tz_music?useSSLfalseserverTimezoneUTC,root, 1061700625);// 3、使用Connection创建Statement对象。 Statement state conn.createStatement();// 4、执行SQL语句。 // execute可执行任何SQL语句返回一个boolean值 // executeQuery执行查询语句返回一个结果集 // executeUpdate执行DML语句返回一个整数 ResultSet res state.executeQuery(select * from tz_music;);// 5、获取数据。 // next将记录指针下移一行若有效则返回true // getXxx通过列名或列索引获取记录指针 while (res.next()){System.out.println(res.getString(music_name));}}catch (Exception e){e.printStackTrace();}}}执行SQL语句的方法使用executeLargeUpdate方法执行DDL和DML语句示例读取ini文件连接并创建数据表; mysql.ini文件drivercom.mysql.cj.jdbc.Driverurljdbc:mysql://127.0.0.1:3306/tz_music?useSSLfalseserverTimezoneUTCuserrootpwd1061700625package com.sxf.sql;import java.io.FileInputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import java.util.Properties;class Sql{private String driver;private String url;private String user;private String pwd;public void initParam(String paramFile) throws Exception{var props new Properties();props.load(new FileInputStream(paramFile));driver props.getProperty(driver);url props.getProperty(url);user props.getProperty(user);pwd props.getProperty(pwd);}public void createTable(String sql) throws Exception{Class.forName(driver);try (Connection conn DriverManager.getConnection(url, user, pwd);Statement state conn.createStatement();){state.executeUpdate(sql);}}}public class Main {public static void main(String[] args) throws Exception{Sql sql new Sql();sql.initParam(mysql.ini);sql.createTable(create table jdbc(id int primary key, name varchar(255)););System.out.println(OK);}}使用PreparedStatement执行SQL语句PreparedStatement的好处与作用预编译SQL语句性能更好无需“拼接”SQL语句编程更简单可以防止SQL注入安全性更好。示例使用PreparedStatement插入记录class Sql{... ...public void insertTable() throws Exception{Class.forName(driver);try (Connection conn DriverManager.getConnection(url, user, pwd);PreparedStatement state conn.prepareStatement(insert into jdbc values(?, ?););){state.setInt(1, 1);state.setString(2, a);state.executeUpdate();state.setInt(1, 2);state.setString(2, a);state.executeUpdate();}}}public class Main {public static void main(String[] args) throws Exception{Sql sql new Sql();sql.initParam(mysql.ini);sql.insertTable();System.out.println(OK);}}使用CallableStatement调用存储过程调用存储过程的SQL语句格式{call 过程名(? ,?, ?...)}其中的问号作为存储过程参数的占位符。存储过程的参数既有传入参数也有传出参数。传入参数通过setXxx()方法为传入参数设置值。传出参数调用registerOutParameter()方法注册该参数通过getXxx()方法获取指定传出参数的值。示例调用存储功能public void callProc() {try {Class.forName(driver);Connection conn DriverManager.getConnection(url, user, pwd);CallableStatement cstmt conn.prepareCall({call add_pro(?, ?, ?)});cstmt.setInt(1, 4);cstmt.setInt(2, 5);// 注册第三个参数是int类型的输出参数 cstmt.registerOutParameter(3, Types.INTEGER);cstmt.execute();System.out.println(cstmt.getInt(3));}catch (Exception e){e.printStackTrace();}}管理结果集可滚动、可更改的结果集可滚动可以使用方法自由移动记录指针的ResultSet。可更新可调用ResultSet的方法来修改记录指针所指记录、特定列的值。示例创建可滚动、可更改的结果集public void query(String sql) throws Exception{Class.forName(driver);try (Connection conn DriverManager.getConnection(url, user, pwd);// 传入控制结果集可滚动、可更新的参数 PreparedStatement state conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);ResultSet res state.executeQuery();){res.last();int rowCount res.getRow();for (int i rowCount; i 0; i--) {res.absolute(i);System.out.println(res.getString(1) - res.getString(2));res.updateString(2, abc);res.updateRow();}}}sql.query(select * from jdbc);处理Blob类型数据Blob(Binary Long Object)二进制长对象用于存储大文件典型的Blob内容是一张图片或一个声音文件。Blob数据插入数据库需要使用PreparedStatement该对象有一个方法setBinaryStream(int index, InputStream x)该方法可以为指定参数传入二进制输入流从而可以实现将Blob数据保存到数据库中。调用ResultSet的getBlob(int index)方法可以从ResultSet里取出Blob数据该方法返回一个Blob对象。Blob对象提供了getBinaryStream()方法来获取该Blob数据的输入流也可以使用Blob对象提供的getBytes()方法直接取出该Blob对象封装的二进制数据。建立数据表时创建一个mediumblob类型的数据列用于保存图片数据。MySQL里数据库里的blob类型最多只能存储64KB内容而mediumblob类型可以存储16MB的内容。示例通过SQL的Blob存储并读取图片数据public void blobDemo(String fileName) throws Exception{Connection conn;PreparedStatement state;PreparedStatement query;Class.forName(driver);try {conn DriverManager.getConnection(url, user, pwd);state conn.prepareStatement(insert into jdbc values (?,?,?), Statement.RETURN_GENERATED_KEYS);query conn.prepareStatement(select image from jdbc);String imageName fileName.substring(fileName.lastIndexOf(\\)1, fileName.lastIndexOf(.));var f new File(fileName);var is new FileInputStream(f);state.setObject(1, 8);state.setObject(2, img);state.setBinaryStream(3, is, (int)f.length());int affect state.executeUpdate();System.out.println(affect);ResultSet res query.executeQuery();if(res.next()){Blob imgBlob res.getBlob(3);var out imgBlob.getBinaryStream();}}catch (Exception e){e.printStackTrace();}}使用ResultSetMetaData分析结果集描述其他数据的数据用以获取关于ResultSet的描述信息。通过getMetaData()方法获得ResultSetMetaData对象。需要一定的系统开销。三个常用方法int getColumnCount()返回改ResultSet的列数量String getColumnName(int column)返回指定索引的列名int getColumnType(int column)返回指定索引的列类型示例分析结果集public void metaData() throws Exception{Class.forName(driver);Connection conn DriverManager.getConnection(url, user, pwd);Statement state conn.createStatement();ResultSet set state.executeQuery(select * from jdbc);ResultSetMetaData rsmd set.getMetaData();System.out.println(rsmd.getColumnCount());System.out.println(rsmd.getColumnName(2));System.out.println(rsmd.getColumnType(2));}使用RowSet包装结果集RowSet接口下包含jdbcRowSet、CachedRowSet、FilteredRowSet、JoinRowSet、WebRowSet常用子接口。jdbcRowSet需要保持与数据库的连接其余4个是离线的RowSet无需保持连接。RowSetFactory和RowSetRowSet常用方法setUrl(String url)、setUsername(String name)、setPassword(String pwd)、setCommand(String sql)、execute()示例通过RowSetFactory使用jdbcRowSetpublic void update(String sql) throws Exception{Class.forName(driver);// 创建RowSetFactory RowSetFactory factory RowSetProvider.newFactory();// 创建实例 JdbcRowSet jdbcRs factory.createJdbcRowSet();// 设置连接信息 jdbcRs.setUrl(url);jdbcRs.setUsername(user);jdbcRs.setPassword(pwd);jdbcRs.setCommand(sql);// 执行查询 jdbcRs.execute();jdbcRs.afterLast();// 向前滚动结果集 while (jdbcRs.previous()){System.out.println(jdbcRs.getString(1));// 修改记录行 jdbcRs.updateString(2, 123);// 提交修改 jdbcRs.updateRow();}}离线RowSet直接将底层数据读入内存中封装成RowSet对象而RowSet对象完全可以当成Java Bean来使用。示例CachedRowSet离线操作SQLpublic void query(String sql) throws Exception{Class.forName(driver);// 获取数据库连接 Connection conn DriverManager.getConnection(url, user, pwd);Statement stmt conn.createStatement();ResultSet res stmt.executeQuery(sql);// 创建RowSetFactory RowSetFactory factory RowSetProvider.newFactory();// 创建实例 CachedRowSet cachedRs factory.createCachedRowSet();// 装填RowSet cachedRs.populate(res);// 关闭资源 res.close();stmt.close();conn.close();cachedRs.afterLast();// 重新获取数据库连接 Connection conn2 DriverManager.getConnection(url, user, pwd);conn2.setAutoCommit(false);// 把对RowSet所做的修改同步到底层数据库 cachedRs.acceptChanges(conn2);}事务处理事务的概念事务ACID特性原子性atomicity、一致性consistency、隔离性isolation、持续性durability事务提交显示提交使用commit自动提交执行DDL或DCL语句或程序正常退出事务回滚显式回滚使用rollback自动回滚系统错误或强行退出普通的提交、回滚都会结束当前事务但回滚到指定中间点因为依然处于事务之中所以不会结束当前事务。JDBC事务支持调动Connection的setAutoCommit()方法来关闭自动提交开启事务。当Connection遇到一个未处理的SQLException异常时系统将会非正常退出事务也会自动回滚。但如果程序捕获了该异常则需要再异常处理块中显式地回滚事务。// 关闭自动提交开启事务 conn.setAutoCommit(false);// 提交事务 conn.commit();// 回滚事务 conn.rollback();// 创建保存点 point conn.setSavePoint();// 回滚到保存点 conn.rollback(point);使用批量更新多条SQL语句将被作为一批操作被同时收集同时提交。使用批量更新也需要先创建一个Statement对象然后利用该对象的addBatch()方法将多条SQL语句同时收集起来最后调用Statement对象的executeLargeBatch()或executeBatch()方法同时执行这些SQL语句。如果在批量更新的addBatch()方法中添加select查询语句程序将直接出现错误。为了让批量操作可以正确地处理错误必须把批量执行的操作视为单个事务如果批量更新在执行过程中失败则让事务回滚到批量操作开始之前的状态。薇乐大道这种效果程序应该在开始批量操作之前先关闭自动提交然后开始收集更新语句当批量操作执行结束后提交事务并恢复之前的自动提交模式。Statement stmt conn.createStatement();stmt.addBatch(sql1);stmt.addBatch(sql2);stmt.addBatch(sql3);... ...stmt.executeLargeBatch();
