原生的JDBC操作数据库(mysql)
1.mysql-connector-java-5.1.45-bin.jar
2.建一个lib文件夹,把jar包粘贴到lib里面,然后把这个jar包变成小奶瓶即可
@Test
public void insertUser() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01", "root", "root");
String sql = "INSERT INTO USER SET NAME='aa',pwd='123';";
Statement st = conn.createStatement();
int executeUpdate = st.executeUpdate(sql);
if (executeUpdate >= 1) {
System.out.println("插入成功!!");
} else {
System.out.println("插入失败");
}
st.close();
conn.close();
}
第二种连接数据库方式
JDBC:
java databases connectivity java数据库连接,用java操作关系型数据库的规则
本质:其实是官方(sun公司)定义的一套操作所有关系型数据库的规则,即接口。
jdbc控制事务
1.事务:一个包含多个步骤的业务操作。如果这个业务被事务管理,则这个步骤要么同时成功,要么同时失败。
2.操作:
1.开启事务 setAutoCommit(boolean autocommit) 调用该方法设置参数为false,即开启事务 在执行sql前开启事务
2.提交事务 commit(); 执行完所有sql是提交事务
3.回滚事务 rollback(); 在catch中回滚事务
3.使用connection来管理事务
jdbc连接池 解释原理
public static void main(String[] args) throws Exception {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/company?characterEncoding=utf8");
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setInitialSize(10);
dataSource.setMaxActive(100);
for(int i = 0;i<10;i++) {
DruidPooledConnection conn = dataSource.getConnection();
System.out.println(conn.isClosed());
}
}
配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db100?characterEncoding=utf8
username=root
password=root
工具类
package com.qs.util;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
public abstract class JDBCUtil {
private static String driver;
private static String url;
private static String username;
private static String password;
private static PreparedStatement ps = null;
private static Connection conn = null;
private static DruidDataSource dds = null;
static {
try {
Properties pop = new Properties();
InputStream is;
is = new FileInputStream("src/jdbc.properties");
pop.load(is);
driver = pop.getProperty("driver");
Class.forName(driver);
url = pop.getProperty("url");
username = pop.getProperty("username");
password = pop.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getconn() {
try {
conn = DriverManager.getConnection(url, username, password);
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static DruidPooledConnection getDruidcon() {
dds = new DruidDataSource();
dds.setDriverClassName(driver);
dds.setUrl(url);
dds.setUsername(username);
dds.setPassword(password);
dds.setInitialSize(10);
dds.setMaxActive(100);
try {
return dds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static PreparedStatement getPropStame(String sql) {
if (ps == null) {
try {
ps = getconn().prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
return ps;
}
public static PreparedStatement getDruidPropStame(String sql) {
if (ps == null) {
try {
ps = getDruidcon().prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
return ps;
}
public static void closeConn(PreparedStatement ps, Connection conn) {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeQueryConn(ResultSet rs, PreparedStatement ps, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeDruidConn(PreparedStatement ps, DruidPooledConnection dds) {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (dds != null) {
try {
dds.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
第三种连接方式
public class DSUtil {
private static DruidDataSource dataSource = new DruidDataSource();
// 返回DataSource
public static DruidDataSource getDataSource() {
dataSource.setUrl("jdbc:mysql://localhost:3306/company?characterEncoding=utf8");
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("root");
return dataSource;
}
}
blic class UsersDaoImpl implements UsersDao {
private QueryRunner runner = new QueryRunner(DSUtil.getDataSource());
@Override//重写登录的方法
public Users checkLogin(Users users) {
try {
return runner.query("select * from tb_users where userid=? and pwd=?", new BeanHandler<Users>(Users.class), users.getUserid(), users.getPwd());
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
@Override//重写分页的方法
public List<Users> findAll(int offset,int pageNumber) {
try {
return runner.query("select * from tb_users limit ?,?",new BeanListHandler<Users>(Users.class),offset,pageNumber);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
@Override//获取总页数
public int getPage() {
try {
return ((Long)runner.query("select count(*) from tb_users",new ScalarHandler())).intValue();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return 0;
}
@Override//重写一个删除的方法
public boolean delete(String id) {
try {
return runner.update("delete from tb_users where userid=?",id)>0;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return false;
}
}