Java连接数据库
package com. kaikeba. learn;
import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. ResultSet;
import java. sql. Statement;
public class Demo {
public static void createTable ( ) throws Exception {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
Connection conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
Statement state = conn. createStatement ( ) ;
state. execute ( "create table `person`(id int,name varchar(32))" ) ;
state. close ( ) ;
conn. close ( ) ;
}
public static void insertInto ( ) throws Exception {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
Connection conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
Statement statement = conn. createStatement ( ) ;
statement. execute ( "insert into person values(1,'张三')" ) ;
conn. close ( ) ;
statement. close ( ) ;
}
public static void deletePerson ( ) throws Exception {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
Connection conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
Statement statement = conn. createStatement ( ) ;
statement. execute ( "delete from person where id = 1" ) ;
conn. close ( ) ;
statement. close ( ) ;
}
public static void updatePerson ( ) throws Exception {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
Connection conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
Statement statement = conn. createStatement ( ) ;
statement. execute ( "update person set name = '李四' where id = 1" ) ;
conn. close ( ) ;
statement. close ( ) ;
}
public static void selectPerson ( ) throws Exception {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
Connection conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
Statement statement = conn. createStatement ( ) ;
ResultSet resultSet = statement. executeQuery ( "select * from person" ) ;
System. out. println ( resultSet) ;
conn. close ( ) ;
statement. close ( ) ;
}
}
预编译
当传入的登陆密码为1' or '1'='1 时,无论数据是否存在,最后都将显示登陆成功。
这是因为出现了SQL注入的问题:
原mysql语句:
select * from mlp_user where username='' and password='';
实际上变为:
select * from mlp_user where username='' and password='1' or '1'='1';
采用预编译的方法解决。
工厂架构
1.Person
package com. java. demo;
import java. util. Objects;
public class Person {
private String username;
private String password;
public Person ( String username, String password ) {
this . username = username;
this . password = password;
}
public Person ( ) {
}
public String getUsername ( ) {
return username;
}
public void setUsername ( String username ) {
this . username = username;
}
public String getPassword ( ) {
return password;
}
public void setPassword ( String password ) {
this . password = password;
}
@Override
public boolean equals ( Object o ) {
if ( this == o) return true ;
if ( o == null || getClass ( ) != o. getClass ( ) ) return false ;
Person person = ( Person) o;
return Objects. equals ( username, person. username) && Objects. equals ( password, person. password) ;
}
@Override
public int hashCode ( ) {
return Objects. hash ( username, password) ;
}
@Override
public String toString ( ) {
return "Person{" +
"username='" + username + '\'' +
", password='" + password + '\'' +
'}' ;
}
}
2.BaseDaoPerson
package com. java. demo;
import java. util. List;
public interface BaseDaoPerson {
boolean insertData ( String username, String password) ;
boolean findByPassword ( String username, String password) ;
boolean findByPassword2 ( String username, String password) ;
List< Person> getAll ( ) ;
}
3.MysqlDaoPerson
package com. java. demo;
import java. sql. * ;
import java. util. ArrayList;
import java. util. List;
public class MysqlDaoPerson implements BaseDaoPerson {
static {
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
} catch ( ClassNotFoundException e) {
e. printStackTrace ( ) ;
}
}
@Override
public boolean insertData ( String username, String password ) {
Connection conn = null ;
Statement state = null ;
try {
conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
state = conn. createStatement ( ) ;
int flag = state. executeUpdate ( "insert into mlp_user values('" + username + "','" + password + "')" ) ;
return flag> 0 ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
try {
conn. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
try {
state. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
}
return false ;
}
@Override
public boolean findByPassword ( String username, String password ) {
Connection conn = null ;
PreparedStatement state = null ;
try {
conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
state = conn. prepareStatement ( "select * from mlp_user where username=? and password=?" ) ;
state. setString ( 1 , username) ;
state. setString ( 2 , password) ;
ResultSet rs = state. executeQuery ( ) ;
return rs. next ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
try {
conn. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
try {
state. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
}
return false ;
}
@Override
public boolean findByPassword2 ( String username, String password ) {
Connection conn = null ;
Statement state = null ;
try {
conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
state = conn. createStatement ( ) ;
ResultSet rs = state. executeQuery ( "select * from mlp_user where username='" + username + "' and password='" + password + "'" ) ;
return rs. next ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
try {
conn. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
try {
state. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
}
return false ;
}
@Override
public List< Person> getAll ( ) {
List< Person> data = new ArrayList < > ( ) ;
Connection conn = null ;
Statement state = null ;
ResultSet rs = null ;
try {
conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
state = conn. createStatement ( ) ;
rs = state. executeQuery ( "select * from mlp_user" ) ;
while ( rs. next ( ) ) {
String username = rs. getString ( "username" ) ;
String password = rs. getString ( "password" ) ;
data. add ( new Person ( username, password) ) ;
}
} catch ( SQLException e) {
e. printStackTrace ( ) ;
} finally {
try {
conn. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
try {
state. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
try {
rs. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
}
return data;
}
}
4.PersonDaoFactory
package com. java. demo;
public class PersonDaoFactory {
public static BaseDaoPerson get ( ) {
return new MysqlDaoPerson ( ) ;
}
}
5.Main
package com. java. demo;
import java. util. List;
import java. util. Scanner;
public class Main {
static Scanner input = new Scanner ( System. in) ;
static BaseDaoPerson dao= PersonDaoFactory. get ( ) ;
public static void main ( String[ ] args ) {
System. out. println ( "欢迎来到某某公司" ) ;
System. out. println ( "请选择您要进行的操作:" ) ;
System. out. println ( "1.注册 2.登陆 3.查询所有用户" ) ;
String menu = input. nextLine ( ) ;
switch ( menu) {
case "1" :
reg ( ) ;
break ;
case "2" :
login ( ) ;
break ;
case "3" :
showAll ( ) ;
break ;
}
}
private static boolean login ( ) {
System. out. println ( "请输入你的用户名:" ) ;
String username = input. nextLine ( ) ;
System. out. println ( "请输入你的密码:" ) ;
String password = input. nextLine ( ) ;
boolean flag = dao. findByPassword ( username, password) ;
System. out. println ( flag? "登陆成功" : "登陆失败" ) ;
return flag;
}
private static List< Person> showAll ( ) {
List< Person> data = dao. getAll ( ) ;
System. out. println ( "您要查询的数据如下:" ) ;
System. out. println ( data) ;
return data;
}
private static boolean reg ( ) {
System. out. println ( "请输入你要注册的用户名:" ) ;
String username = input. nextLine ( ) ;
System. out. println ( "请输入你要注册的密码:" ) ;
String password = input. nextLine ( ) ;
boolean flag = dao. insertData ( username, password) ;
System. out. println ( flag? "注册成功" : "注册失败" ) ;
return flag;
}
}
事务
事务
事务的开启:conn.setAutoCommit(false);
事务的回滚:conn.rollback();
事务的提交:conn.commit();
事务的面试题回顾:
<1>事务的四大特征:
原子性;持久性;隔离性;一致性;
<2>脏读、不可重复读、幻读?
脏读:读到了一个事务的为提交数据;
不可重复读:前后两次读取得结果不一致(中间被其他的事务更改了数据)
幻读:两个事务AB同时在执行DML语句时,B修改了A修改过的数据,导致A查询时就像出现幻觉一样。(A修改的内容A看不到了)
<3>事务的隔离级别:
读未提交;
读已提交;
不可重复读;
顺序读。v
package com. java. demo1;
import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. SQLException;
import java. sql. Statement;
public class Main {
public static void main ( String[ ] args ) {
Connection conn = null ;
Statement state = null ;
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
state = conn. createStatement ( ) ;
conn. setAutoCommit ( false ) ;
state. execute ( "insert into mlp_user values('张三','123')" ) ;
if ( 1 != 2 ) {
throw new RuntimeException ( "停电了" ) ;
}
conn. commit ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
try {
conn. rollback ( ) ;
} catch ( SQLException ex) {
ex. printStackTrace ( ) ;
}
} finally {
try {
conn. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
try {
state. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
}
Properties 文件作为配置文件
properties类:是Java中Map集合的实现类;
.properties文件:用于描述一组键值对!
.properties文件可以被快速的转换为Properties类的对象。
文件内容格式:
文件中内容都是字符串,一对键与值之间等号连接,多对键值对之间换行连接。
例如:
url=xxx
user=xxx
password=xxx
如何将.properties文件转换为properties对象:
1.创建properties对象
Properties ppt = new Properties();
2.字节流输入,指向.properties文件
InputStream is = new FileInputStream("文件地址");
3.将字节流输入加载给properties对象
ppt.load(is);
批处理
1.正常的批处理
package com. java. demo2;
import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. SQLException;
import java. sql. Statement;
public class Main1 {
public static void main ( String[ ] args ) {
Connection conn = null ;
Statement state = null ;
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
state = conn. createStatement ( ) ;
state. addBatch ( "insert into mlp_user values('王五','123')" ) ;
state. addBatch ( "update mlp_user set password = '124' where username='王五'" ) ;
state. executeBatch ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
try {
state. clearBatch ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
try {
state. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
try {
conn. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
}
2.预处理的批处理
package com. java. demo2;
import java. sql. * ;
public class Main2 {
public static void main ( String[ ] args ) {
Connection conn = null ;
PreparedStatement state = null ;
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
conn = DriverManager. getConnection ( "jdbc:mysql://localhost:3306/demo" , "root" , "" ) ;
state = conn. prepareStatement ( "update mlp_user set password =? where username=? " ) ;
state. setString ( 1 , "124" ) ;
state. setString ( 2 , "张三" ) ;
state. addBatch ( ) ;
state. setString ( 1 , "123" ) ;
state. setString ( 2 , "王五" ) ;
state. addBatch ( ) ;
state. executeBatch ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
try {
state. clearBatch ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
try {
state. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
try {
conn. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
}
连接池
连接池
类似于线程池,连接池也是来缓存"东西"的,只是它缓存的是连接。
当我们需要使用连接的时候,不用创建,直接从连接池中获取即可!
当连接池中有空闲连接的时候,可以直接给程序使用;
当连接池中没有空闲连接,且连接池未满时,会创建连接给程序使用,使用结束后会缓存连接;
当连接池中没有空闲连接,且连接池已满连接时,则排队等空闲连接出现。
注意:
在连接池中的连接对象在操作数据时,操作完毕的时候,仍然要关闭连接(close());
由于连接池的连接在设计时,采用的是动态代理模式+修饰者模式,所以当我们调用close()方法时,代理不会关闭连接,而是将连接重新放入到连接池中。
dbcp连接池
1.将.properties文件放入src文件夹下
导入dbcp.jar包
导入pool.jar包
2.引入dbcp.properties文件
3.创建properties新对象,加载dbcp.properties文件
4.通过连接池的工厂类创建连接池
5.通过连接池获取连接
德鲁伊连接池
1.导入jar包 druid.jar
2.将.properties文件放入src文件夹下,引入.properties文件
3.创建新的properties类对象,并加载.properties文件
4.使用连接池的工厂类创建连接池方法,创建连接池
5.获取连接池
DBCP连接池
package com. java. demo3;
import com. java. demo. PersonDaoFactory;
import org. apache. commons. dbcp. BasicDataSourceFactory;
import javax. sql. DataSource;
import java. io. FileInputStream;
import java. io. FileNotFoundException;
import java. io. IOException;
import java. io. InputStream;
import java. sql. Connection;
import java. sql. Statement;
import java. util. Properties;
public class Main1 {
public static void main ( String[ ] args) throws Exception {
InputStream is = Main1. class. getClassLoader ( ) . getResourceAsStream ( "dbcp.properties" ) ;
Properties ppt = new Properties ( ) ;
ppt. load ( is) ;
DataSource ds = BasicDataSourceFactory. createDataSource ( ppt) ;
Connection conn = ds. getConnection ( ) ;
Statement state = conn. createStatement ( ) ;
state. execute ( "insert into mlp_user values('赵六','125')" ) ;
state. close ( ) ;
conn. close ( ) ;
}
}
DBCPUtil工具类
package com. java. demo3;
import org. apache. commons. dbcp. BasicDataSourceFactory;
import org. apache. commons. dbcp. DataSourceConnectionFactory;
import javax. sql. DataSource;
import java. io. IOException;
import java. io. InputStream;
import java. sql. Connection;
import java. sql. ResultSet;
import java. sql. SQLException;
import java. sql. Statement;
import java. util. Properties;
public class DBCPUtil {
private static DataSource ds = null ;
static {
InputStream is = DBCPUtil. class. getClassLoader ( ) . getResourceAsStream ( "dbcp.properties" ) ;
Properties ppt = new Properties ( ) ;
try {
ppt. load ( is) ;
ds = BasicDataSourceFactory. createDataSource ( ppt) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
}
public static Connection getConnection ( ) {
try {
return ds. getConnection ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
return null ;
}
}
public static void close ( Connection conn, Statement state, ResultSet result ) {
if ( result != null ) {
try {
result. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( state != null ) {
try {
state. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( conn != null ) {
try {
conn. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
}
Druid连接池
package com. java. demo3;
import com. alibaba. druid. pool. DruidDataSource;
import com. alibaba. druid. pool. DruidDataSourceFactory;
import javax. sql. DataSource;
import java. io. FileInputStream;
import java. io. FileNotFoundException;
import java. io. InputStream;
import java. sql. Connection;
import java. sql. Statement;
import java. util. Properties;
public class Main2 {
public static void main ( String[ ] args) throws Exception {
InputStream is = Main2. class. getClassLoader ( ) . getResourceAsStream ( "druid.properties" ) ;
Properties ppt = new Properties ( ) ;
ppt. load ( is) ;
DataSource ds = DruidDataSourceFactory. createDataSource ( ppt) ;
Connection conn = ds. getConnection ( ) ;
Statement state = conn. createStatement ( ) ;
state. execute ( "insert into mlp_user values('孙琦','126')" ) ;
conn. close ( ) ;
state. close ( ) ;
}
}
DruidUtil工具类
package com. java. demo3;
import com. alibaba. druid. pool. DruidDataSourceFactory;
import org. apache. commons. dbcp. DataSourceConnectionFactory;
import javax. sql. DataSource;
import java. io. IOException;
import java. io. InputStream;
import java. sql. Connection;
import java. sql. ResultSet;
import java. sql. SQLException;
import java. sql. Statement;
import java. util. Properties;
public class DruidUtil {
private static DataSource data = null ;
static {
InputStream is = DruidUtil. class. getClassLoader ( ) . getResourceAsStream ( "Druid.properties" ) ;
Properties ppt = new Properties ( ) ;
try {
ppt. load ( is) ;
data = DruidDataSourceFactory. createDataSource ( ppt) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
}
public static Connection getConnection ( ) {
try {
return data. getConnection ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
return null ;
}
}
public static void close ( Connection conn, Statement state, ResultSet result ) {
if ( result != null ) {
try {
result. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( conn != null ) {
try {
conn. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( state != null ) {
try {
state. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
}