一,模拟用户登录过程

–1,需求

select * from user where name=‘jack’ and pwd=‘123’
如果查到了数据,就可以登录
如果没查到,就登录失败

–2,测试

package cn.tedu.jdbc;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;//模拟用户登录的过程--查库
public class Login {public static void main(String[] args) throws Exception {//利用工具类  1,注册驱动 2,获取连接Connection c = JDBCUtils.get();//3,获取传输器Statement s = c.createStatement();//4,执行SQLString sql ="select * from user where name='jack' and pwd='xyz'";ResultSet r = s.executeQuery(sql);//5,解析结果if( r.next() ){ //判断,查到了就登录System.out.println("欢迎您回来~");}else{System.out.println("登录失败~");}//6,关闭资源r.close();s.close();c.close();}
}

–3,程序优化

动态拼接SQL语句

package cn.tedu.jdbc;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;//模拟用户登录的过程--查库
public class Login {public static void main(String[] args) throws Exception {//利用工具类  1,注册驱动 2,获取连接Connection c = JDBCUtils.get();//3,获取传输器Statement s = c.createStatement();//4,执行SQLSystem.out.println("请您输入账号:");String a = new Scanner(System.in).nextLine();System.out.println("请您输入密码:");String b = new Scanner(System.in).nextLine();//中间动态拼接字符串: "+？？？+"//String sql ="select * from user where name='jack' and pwd='xyz'";String sql ="select * from user where name='"+a+"' and pwd='"+b+"'";ResultSet r = s.executeQuery(sql);//5,解析结果if( r.next() ){ //判断,查到了就登录System.out.println("欢迎您回来~");}else{System.out.println("登录失败~");}//6,关闭资源r.close();s.close();c.close();}
}

二,SQL攻击/注入

–1,概述

在SQL语句拼接参数的过程中,出现了特殊的符号# ,改变了SQL的语义
上面的案例中,当用户输入固定的用户名:jack’# 时,不必输入密码竟然可以登录!!!

SELECT * FROM USER WHERE NAME='jack' #' and pwd='xyz'
此时,#之后的条件会被注释掉.

–2,解决方案

使用新的传输器PreparedStatement 代替现在的传输器Statement
解决了SQL攻击的问题,把特殊符号#当做一个普通的字符在用,不会当做注释来解析.

–3,修改代码

package cn.tedu.jdbc;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;//模拟用户登录的过程--查库
public class Login {public static void main(String[] args) throws Exception {//利用工具类  1,注册驱动 2,获取连接Connection c = JDBCUtils.get();//4,执行SQLSystem.out.println("请您输入账号:");String a = new Scanner(System.in).nextLine();System.out.println("请您输入密码:");String b = new Scanner(System.in).nextLine();//SQL的骨架,?叫占位符String sql = "select * from user where name=? and pwd=?";//3,获取传输器PreparedStatement s = c.prepareStatement(sql);//给?设置值s.setObject(1,a);//给第一个问号,设置a的值s.setObject(2,b);//给第二个问号,设置b的值ResultSet r = s.executeQuery();//直接执行拼好的就行了//5,解析结果if( r.next() ){ //判断,查到了就登录System.out.println("欢迎您回来~");}else{System.out.println("登录失败~");}//6,关闭资源r.close();s.close();c.close();}
}

–4,两种传输器的区别

Statement:父接口+SQL攻击+SQL语句需要手动拼接参数(复杂)+低效
PreparedStatement:子接口,用了父接口的所有功能,还进行了优化+解决了SQL攻击+SQL骨架(简化了)+高效

三,练习新的传输器

–1,测试

package cn.tedu.jdbc;import java.sql.Connection;
import java.sql.PreparedStatement;//使用新的传输器  向dept表里新增一条数据
public class Test3 {public static void main(String[] args) throws Exception {//利用工具类Connection c = JDBCUtils.get();//准备SQL骨架String sql = "insert into dept values(null,?,?)";//获取新的传输器--高效,安全,SQL简洁PreparedStatement s = c.prepareStatement(sql);//设置参数s.setObject(1,"web前端");s.setObject(2,"大钟寺");//executeUpdate()执行增删改的SQLs.executeUpdate();//关闭资源s.close();c.close();}
}

–2,标准的关闭资源

package cn.tedu.jdbc;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;//使用新的传输器  向dept表里新增一条数据
public class Test3 {public static void main(String[] args){Connection c = null; //为了让finally里能够使用变量,所以扩大作用范围PreparedStatement s = null;try{//利用工具类c = JDBCUtils.get();//准备SQL骨架String sql = "insert into dept values(null,?,?)";//获取新的传输器--高效,安全,SQL简洁s = c.prepareStatement(sql);//设置参数s.setObject(1,"web前端2");s.setObject(2,"大钟寺2");//executeUpdate()执行增删改的SQLs.executeUpdate();}catch(Exception e){System.out.println("插入失败~~");}finally{//第一会被执行--关闭资源//为了防止空指针异常--加一个非空的判断if(s != null){try {s.close();} catch (SQLException throwables) {throwables.printStackTrace();}finally{//防止发生异常导致s没被关闭,手动置空,等着GC垃圾回收了.s = null; }}if(c != null) {try {c.close();} catch (SQLException throwables) {throwables.printStackTrace();}finally{//防止发生异常导致c没被关闭,手动置空,等着GC垃圾回收了.c = null; }}}}
}

–3,优化关闭资源

封装工具类,提供close()

package cn.tedu.jdbc;import java.sql.*;//封装了一些常用方法,提高代码的复用性--高内聚
public class JDBCUtils {/*** 调用者即将得到一个数据库的连接对象Connection* @return 表示了和数据库的连接* @throws Exception*/static public Connection get() throws Exception{//1,注册驱动Class.forName("com.mysql.jdbc.Driver");//2,获取连接String url = "jdbc:mysql://localhost:3306/cgb211001?characterEncoding=utf8";Connection c = DriverManager.getConnection(url, "root", "root");return c; //交给调用者}/*** 关闭资源,调用者告诉close()即将关闭啥资源*/static public void close(ResultSet r, PreparedStatement s,Connection c){if(r != null){//防止发生空指针异常try {r.close();} catch (SQLException throwables) {throwables.printStackTrace();}finally{//防止发生异常导致r没被关闭,手动置空,等着GC垃圾回收了.r = null;}}if(s!=null) {try {s.close();} catch (SQLException throwables) {throwables.printStackTrace();}finally{//防止发生异常导致s没被关闭,手动置空,等着GC垃圾回收了.s = null;}}if(c!=null) {try {c.close();} catch (SQLException throwables) {throwables.printStackTrace();}finally{c = null;}}}}

改造代码

package cn.tedu.jdbc;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;//使用新的传输器  向dept表里新增一条数据
public class Test3 {public static void main(String[] args){Connection c = null; //为了让finally里能够使用变量,所以扩大作用范围PreparedStatement s = null;try{//利用工具类c = JDBCUtils.get();//准备SQL骨架String sql = "insert into dept values(null,?,?)";//获取新的传输器--高效,安全,SQL简洁s = c.prepareStatement(sql);//设置参数s.setObject(1,"web前端2");s.setObject(2,"大钟寺2");//executeUpdate()执行增删改的SQLs.executeUpdate();}catch(Exception e){System.out.println("插入失败~~");}finally {//第一会被执行--关闭资源JDBCUtils.close(null,s,c);//使用工具类的close()}}
}

四,Maven

–1,概述

现在: 下载jar包,管理jar包,编译jar包.
以后: 这套关于jar包的操作全都交给Maven
作用: 是一个项目构建工具,创建Maven项目,maven会自动管理jar包(下载,保存,编译)

–2,Maven的四个特性

仓库repository:

远程仓库/中央仓库: 本质就是一个国外网址
镜像仓库: 本质就是一个国内的网址
本地仓库: 自己创建一个文件夹,用来存放 maven从镜像仓库 下载好的jar包(D:\Java\maven\resp)

坐标

用来存放jar包和查找jar包的定位方式.本质上就是一层一层的文件夹
groupId: 组id,通常值是公司的域名
artifactId: 项目id,通常值是项目名称
version: 版本,jar包也有很多版本

依赖

可以指定项目需要用到的jar包的坐标,maven会自动关联jar包

<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.32</version>
</dependency>

命令

是Maven的一大特色,结合着IDEA来使用一些命令
clean: 清空缓存
install: 安装

–3,操作步骤

1, 下载 / 安装: 解压压缩包就可以了

D:\Java\maven\apache-maven-3.6.3

2, 修改settings.xml文件,配置信息(镜像仓库,本地仓库)

打开,解压好的 apache-maven-3.6.3\conf\settings.xml文件

<?xml version="1.0" encoding="UTF-8"?><!--
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License athttp://www.apache.org/licenses/LICENSE-2.0Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
--><!--| This is the configuration file for Maven. It can be specified at two levels:||  1. User Level. This settings.xml file provides configuration for a single user,|                 and is normally provided in ${user.home}/.m2/settings.xml.||                 NOTE: This location can be overridden with the CLI option:||                 -s /path/to/user/settings.xml||  2. Global Level. This settings.xml file provides configuration for all Maven|                 users on a machine (assuming they're all using the same Maven|                 installation). It's normally provided in|                 ${maven.conf}/settings.xml.||                 NOTE: This location can be overridden with the CLI option:||                 -gs /path/to/global/settings.xml|| The sections in this sample file are intended to give you a running start at| getting the most out of your Maven installation. Where appropriate, the default| values (values used when the setting is not specified) are provided.||-->
<settings xmlns="http://maven.apache.org/SETTINGS/1.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/SETTINGS/1.0.0 http://maven.apache.org/xsd/settings-1.0.0.xsd"><!-- localRepository| The path to the local repository maven will use to store artifacts.|| Default: ${user.home}/.m2/repository --><localRepository>D:\Java\maven\resp</localRepository><!-- interactiveMode| This will determine whether maven prompts you when it needs input. If set to false,| maven will use a sensible default value, perhaps based on some other setting, for| the parameter in question.|| Default: true<interactiveMode>true</interactiveMode>--><!-- offline| Determines whether maven should attempt to connect to the network when executing a build.| This will have an effect on artifact downloads, artifact deployment, and others.|| Default: false<offline>false</offline>--><!-- pluginGroups| This is a list of additional group identifiers that will be searched when resolving plugins by their prefix, i.e.| when invoking a command line like "mvn prefix:goal". Maven will automatically add the group identifiers| "org.apache.maven.plugins" and "org.codehaus.mojo" if these are not already contained in the list.|--><pluginGroups><!-- pluginGroup| Specifies a further group identifier to use for plugin lookup.<pluginGroup>com.your.plugins</pluginGroup>--></pluginGroups><!-- proxies| This is a list of proxies which can be used on this machine to connect to the network.| Unless otherwise specified (by system property or command-line switch), the first proxy| specification in this list marked as active will be used.|--><proxies><!-- proxy| Specification for one proxy, to be used in connecting to the network.|<proxy><id>optional</id><active>true</active><protocol>http</protocol><username>proxyuser</username><password>proxypass</password><host>proxy.host.net</host><port>80</port><nonProxyHosts>local.net|some.host.com</nonProxyHosts></proxy>--></proxies><!-- servers| This is a list of authentication profiles, keyed by the server-id used within the system.| Authentication profiles can be used whenever maven must make a connection to a remote server.|--><servers><!-- server| Specifies the authentication information to use when connecting to a particular server, identified by| a unique name within the system (referred to by the 'id' attribute below).|| NOTE: You should either specify username/password OR privateKey/passphrase, since these pairings are|       used together.|<server><id>deploymentRepo</id><username>repouser</username><password>repopwd</password></server>--><!-- Another sample, using keys to authenticate.<server><id>siteServer</id><privateKey>/path/to/private/key</privateKey><passphrase>optional; leave empty if not used.</passphrase></server>--></servers><!-- mirrors| This is a list of mirrors to be used in downloading artifacts from remote repositories.|| It works like this: a POM may declare a repository to use in resolving certain artifacts.| However, this repository may have problems with heavy traffic at times, so people have mirrored| it to several places.|| That repository definition will have a unique id, so we can create a mirror reference for that| repository, to be used as an alternate download site. The mirror site will be the preferred| server for that repository.|--><mirrors><!-- mirror| Specifies a repository mirror site to use instead of a given repository. The repository that| this mirror serves has an ID that matches the mirrorOf element of this mirror. IDs are used| for inheritance and direct lookup purposes, and must be unique across the set of mirrors.|<mirror><id>mirrorId</id><mirrorOf>repositoryId</mirrorOf><name>Human Readable Name for this Mirror.</name><url>http://my.repository.com/repo/path</url></mirror>--><!-- 达内私服地址 --><!--<mirror><id>nexus</id><name>Tedu Maven</name><mirrorOf>*</mirrorOf><url>http://maven.tedu.cn/nexus/content/groups/public/</url></mirror>--><!--阿里私服地址--><mirror><id>ali</id><name>ali Maven</name><mirrorOf>*</mirrorOf><url>https://maven.aliyun.com/repository/public/</url></mirror><!--<mirror><id>nexus-aliyun</id><mirrorOf>*</mirrorOf><name>Nexus aliyun</name><url>http://maven.aliyun.com/nexus/content/groups/public</url></mirror> <mirror><id>aliyunmaven</id><mirrorOf>*</mirrorOf><name>阿里云公共仓库</name><url>https://maven.aliyun.com/repository/public</url></mirror>--></mirrors><!-- profiles| This is a list of profiles which can be activated in a variety of ways, and which can modify| the build process. Profiles provided in the settings.xml are intended to provide local machine-| specific paths and repository locations which allow the build to work in the local environment.|| For example, if you have an integration testing plugin - like cactus - that needs to know where| your Tomcat instance is installed, you can provide a variable here such that the variable is| dereferenced during the build process to configure the cactus plugin.|| As noted above, profiles can be activated in a variety of ways. One way - the activeProfiles| section of this document (settings.xml) - will be discussed later. Another way essentially| relies on the detection of a system property, either matching a particular value for the property,| or merely testing its existence. Profiles can also be activated by JDK version prefix, where a| value of '1.4' might activate a profile when the build is executed on a JDK version of '1.4.2_07'.| Finally, the list of active profiles can be specified directly from the command line.|| NOTE: For profiles defined in the settings.xml, you are restricted to specifying only artifact|       repositories, plugin repositories, and free-form properties to be used as configuration|       variables for plugins in the POM.||--><profiles><!-- profile| Specifies a set of introductions to the build process, to be activated using one or more of the| mechanisms described above. For inheritance purposes, and to activate profiles via <activatedProfiles/>| or the command line, profiles have to have an ID that is unique.|| An encouraged best practice for profile identification is to use a consistent naming convention| for profiles, such as 'env-dev', 'env-test', 'env-production', 'user-jdcasey', 'user-brett', etc.| This will make it more intuitive to understand what the set of introduced profiles is attempting| to accomplish, particularly when you only have a list of profile id's for debug.|| This profile example uses the JDK version to trigger activation, and provides a JDK-specific repo.<profile><id>jdk-1.4</id><activation><jdk>1.4</jdk></activation><repositories><repository><id>jdk14</id><name>Repository for JDK 1.4 builds</name><url>http://www.myhost.com/maven/jdk14</url><layout>default</layout><snapshotPolicy>always</snapshotPolicy></repository></repositories></profile>--><!--| Here is another profile, activated by the system property 'target-env' with a value of 'dev',| which provides a specific path to the Tomcat instance. To use this, your plugin configuration| might hypothetically look like:|| ...| <plugin>|   <groupId>org.myco.myplugins</groupId>|   <artifactId>myplugin</artifactId>||   <configuration>|     <tomcatLocation>${tomcatPath}</tomcatLocation>|   </configuration>| </plugin>| ...|| NOTE: If you just wanted to inject this configuration whenever someone set 'target-env' to|       anything, you could just leave off the <value/> inside the activation-property.|<profile><id>env-dev</id><activation><property><name>target-env</name><value>dev</value></property></activation><properties><tomcatPath>/path/to/tomcat/instance</tomcatPath></properties></profile>--></profiles><!-- activeProfiles| List of profiles that are active for all builds.|<activeProfiles><activeProfile>alwaysActiveProfile</activeProfile><activeProfile>anotherAlwaysActiveProfile</activeProfile></activeProfiles>-->
</settings>

3, IDEA整合Maven

–4,使用maven

创建Maven工程

File - New - Project - 选择Maven - next - 输入工程名 - Finish

在IDEA里配置maven

五,使用Maven工程开发jdbc程序

–1,修改pom.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>cn.tedu</groupId><artifactId>cgb2110maven02</artifactId><version>1.0-SNAPSHOT</version><!-- 给工程添加 jar包依赖 --><dependencies><!-- 添加jdbc的jar包 --><dependency><!--描述jar包的组id,通常值是域名--><groupId>mysql</groupId><!--描述jar包的项目id,通常值是项目名称--><artifactId>mysql-connector-java</artifactId><!--描述jar包的版本号 , 可以自己指定8.0.24 或者 5.1.48 --><version>5.1.48</version></dependency></dependencies>
</project>

–2,编写jdbc的程序

package cn.tedu.jdbc;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;//利用新的传输器.删除dept里的一条记录
public class Test1 {public static void main(String[] args) {Connection c = null;PreparedStatement p = null;try {//1,注册驱动Class.forName("com.mysql.jdbc.Driver");//2,获取连接
String url="jdbc:mysql://localhost:3306/cgb211001?characterEncoding=utf8";c = DriverManager.getConnection(url,"root","root");//3,获取传输器String sql ="delete from dept where deptno=?";p = c.prepareStatement(sql);//4,执行SQLp.setObject(1,1);//给1个问号的位置,设置的值是1p.executeUpdate();//用来执行增删改的SQL,会返回对数据库的影响行数//5,解析结果集--查询时才会有结果集,这里不需要}catch (Exception e){System.out.println("删除失败~~~");}finally {//6,释放资源//防止空指针异常if(p!=null){try {p.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if(c!=null) {try {c.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}}
}