?serverTimezone=UTC//设置时区<The server time zone value 'ä¸å›½æ ‡å‡†æ—¶é—´' is unrecognized or represents more than one time zone>
net stop mysql//mysql关闭指令
net start mysql//mysql开启数据库指令
java连接数据库使用JDBC
java.sql.*,javax.sql.*这两个包只是接口类
java连接数据库：
-
构建连接
-
注册驱动
-
确定对岸目标
-
try {
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("注册驱动成功!");
} catch (ClassNotFoundException e1) {
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url="jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
Connection conn = null;
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "1234567");
-
执行操作
-
Statement(执行者):
-
1.executeQuery()执行select语句，返回结果放在ResultSet
-
2.使用executeUpdate()执行insert/update/delete,返回修改的行数
-
3.一个statement对象一次只能执行一个命令
-
Result(返回结果集)
-
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
ResultSet rs = stmt.executeQuery("select bookname from t_book order by bookid");
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
//执行SQL语句
int result = stmt.executeUpdate(sql);
-
释放连接
-
1.next()判断是否还有下一条记录
-
2.获取单元格，getint(),getstring(),getdouble().
-
Connection.close();
-
while(rs.next())
{
System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));//可以是单元格的序号以及列名
}
rs.close();
stmt.close();
import java.sql.*;
public class SelectTest {
public static void main(String[] args){
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
return;
}
String url="jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "1234567");
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
//执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
//开始遍历ResultSet数据
while(rs.next())
{
System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
}
rs.close();
stmt.close();
} catch (SQLException e){
e.printStackTrace();
}
finally
{
try
{
if(null != conn)
{
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
}
JDBC事务处理
关闭自动提交，实现多语句同一事务
- connection.setAutoCommit(false);关闭自动提交
- connection.commit();提交事务
- connection.rollback();回滚事务,如果被提交就不可以回滚。
- 保存点机制
- connection.setSavepoint();
- connection.roolback(Savepoint);
// 构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "1234567");
conn.setAutoCommit(false);
insertBook(conn, "insert into t_book values(101, 'aaaa', 10)");
insertBook(conn, "insert into t_book values(102, 'bbbb', 10)");
insertBook(conn, "insert into t_book values(103, 'cccc', 10)");
Savepoint phase1 = conn.setSavepoint(); //设置一个保存点
insertBook(conn, "insert into t_book values(104, 'cccc', 10)");
insertBook(conn, "insert into t_book values(105, 'cccc', 10)");
conn.rollback(phase1); //回滚到phase1保存点，即上面2行无效
conn.commit();
PreparedStatement（预编译）
PreparedStatement是Statement接口的子接口，其实现在JDBC驱动类中， 属于预处理操作，与直接使用Statement不同，PreparedStatement在操作时，是预先在数据表中准备好了一条SQL语句，但是此SQL语句的具体内容暂时不设置，而是之后在进行设置。
- 可以更为安全的执行SQL，防止注入攻击
- 防止繁琐的字符串拼接和
- 直接设置对象而不需要转换为字符串
- PreparedStatement使用预编译速度相对Statement快的多
常用方法
1：executeQuery():在此PreparedStatement对象中执行SQL语句，并返回该查询生成的ResultSet对象。
2：executeUpdate():在此PreparedStatement对象中执行SQL语句，该语句必须是一个SQL数据操作语言(Date Manipulation Language，DML)语句，比如insert、update、delete语句；或者是无返内容的SQL语句，比如DDL语句。
3：execute():在此PreparedStatement对象中执行SQL语句，该语句可以是任何种类的SQL语句。
4：getMetaData():获取包含有关ResultSet对象列信息的ResultSetMetaData对象，ResultSet对象将在此执行PreparedStatement对象时返回。
5：getParameterMetaData():获取此PreparedStatement对象的参数的编号、类型和属性。
注意：
- 注意sql语句的空格，SQL语句换行加空格
- 使用？时应注意设置参数
//使用setstring()时字符串不用加单引号
/数据库插入语句//
String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";
//构建数据库执行者
PreparedStatement pstmt = conn.prepareStatement(sql);
//执行SQL语句
int bookid = 10;
String bookName = "Effective Java";
int price = 50;
pstmt.setInt(1, bookid);
pstmt.setString(2, bookName);
pstmt.setInt(3, price);
int result = pstmt.executeUpdate();
//数据库模糊查询语句//
String sql = " select * from mall where name like ? ";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"%"+s+"%");
ResultSet resultSet = preparedStatement.executeQuery();
///数据库查询语句/
String sql = " select * from mall where name =? ";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,s);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
Shopping shopping = new Shopping();
shopping.setId(resultSet.getInt(1));
shopping.setName(resultSet.getString(2));
shopping.setTime(resultSet.getString(3));
stringArrayList.add(shopping);
}
//数据库删除语句///
String sql = "" +
" delete from mall " +
" where id=? ";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,i);
preparedStatement.execute();
数据库插入语句
String sql = "" +
"insert into mall" +
"(id,name,time)" +
"values(?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,shop.getId());
preparedStatement.setString(2,shop.getName());
preparedStatement.setString(3,shop.getTime());
preparedStatement.execute();
//数据库更新语句/
String sql = "" +
" update mall " +
" set name=?,time=? " +
" where id=? ";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,shop.getName());
preparedStatement.setString(2,shop.getTime());
preparedStatement.setInt(3,shop.getId());
preparedStatement.executeUpdate();
批量提交数据
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "123456");
String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";
//构建数据库执行者
PreparedStatement pstmt = conn.prepareStatement(sql);
//执行SQL语句
String bookName = "aaaaaaaaaaaaaaaa";
int price = 50;
//values(1, 'Effective Java', 50)
for(int i=200;i<210;i++)
{
pstmt.setInt(1, i);
pstmt.setString(2, bookName);
pstmt.setInt(3, price);
pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();
ResultSetMetaData
可以获取resultset返回的属性(如，每一行的名字类型)，使用ResultSetMetaData解析ResultSet：
- getColumnCount(),返回结果的列数
- getColumnClassName(i),返回第i列的数据的java类名
- getColumnTypeName(i),返回第i列的数据库类型名称
- getColumnType(i),返回第I列的SQL类型
//构建Java和数据库之间的桥梁：URL，用户名，密码
conn = DriverManager.getConnection(url, "root", "123456");
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
//执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
//获取结果集的元数据
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
for(int i=1;i<=cols;i++)
{
System.out.println(meta.getColumnName(i) + "," + meta.getColumnTypeName(i));
}
rs.close();
stmt.close();
ResultSet(语句返回结果集)
查询数据库时，返回的是一个二维的结果集，需要用到ResultSet来遍历结果集，获取每一行的数据。
- ResultSet.next():将光标从当前位置向前移动一行。ResultSet游标最初位于第一行之前；第一个对方法的调用接下来使第一行成为当前行；第二个调用使第二行成为当前行，依此类推。当对next方法的调用返回false时，光标将位于最后一行之后。
- ResultSet.getString(columnlabel),或者getString(columnindex),可以传入返回结果集的序号顺序或者数据库列名
数据库连接池
原理
数据库操作的构建连接与释放连接浪费资源，所以应该通过运用共享技术实现数据库连接池（享元模式）
- 降低系统中数据库连接Connection对象的数量
- 将数据库服务器的连接响应消耗
- 提高Connection获取的响应速度
享元模式：经典设计模式的一种，当一个系统中存在大量相同的对象时，由于这类对象的大量使用，会造成系统内存的浪费，可以使用享元模式来减少系统中对象的数量。
基本属性
- 初始数：初始数据库连接的数量
- 最大数：最大数据库连接的数量
- 增量：用完后增加的数量
- 超时时间：
C3P0连接池
import java.sql.Connection;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Factory1 {
private static ComboPooledDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass( "com.mysql.jdbc.Driver" );
dataSource.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
dataSource.setUser("root");
dataSource.setPassword("1234567");
// the settings below are optional -- c3p0 can work with defaults
dataSource.setMinPoolSize(5);//最小元素为5个
dataSource.setAcquireIncrement(5);//增量为5个
dataSource.setMaxPoolSize(20);//最大连接池数量为5个
// The DataSource dataSource is now a fully configured and usable pooled DataSource
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
import java.sql.Connection;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Factory2 {
private static ComboPooledDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new ComboPooledDataSource();
//dataSource 自动加载c3p0-config.xml文件
// The DataSource dataSource is now a fully configured and usable pooled DataSource
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
<?xml version="1.0" encoding="UTF-8"?>//名字必须为c3p0-config.xml
<c3p0-config>
<default-config> <!-- 默认配置 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
Druid连接池
import java.sql.Connection;
import com.alibaba.druid.pool.DruidDataSource;
public class DruidFactory1 {
private static DruidDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test");
dataSource.setInitialSize(5);
dataSource.setMinIdle(1);
dataSource.setMaxActive(10);
// 启用监控统计功能 dataSource.setFilters("stat");//
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidFactory2 {
private static DruidDataSource dataSource = null;
public static void init() throws Exception {
Properties properties = new Properties();
InputStream in = DruidFactory2.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(in);
dataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
in.close();
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
//druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=123456
filters=stat
initialSize=2
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=200
测试类
import java.sql.*;
public class SelectTest {
public static void main(String[] args){
Connection conn = null;
try {
//从c3p0获取
//conn = C3p0Factory1.getConnection();
//conn = C3p0Factory2.getConnection();
//从Druid获取
//conn = DruidFactory1.getConnection();
conn = DruidFactory2.getConnection();
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功！");
//执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
//开始遍历ResultSet数据
while(rs.next())
{
System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
}
rs.close();
stmt.close();
} catch (Exception e){
e.printStackTrace();
} finally {
try {
if(null != conn) {
conn.close();
}
} catch (SQLException e){
e.printStackTrace();
}
}
}
}
扫一扫
1171
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
