一、 基本操作
四条语句:增,删,改,查。
1、#添加数据
insert into student(id , name, gender) values (123456,'张三' ,'男');
2、#删除数据
delete from student where id =1;
3、#修改数据
update student set name = '安琪' ,gender = '女' where id = 2;
4、#查询数据
select * from student ;
select * from student where id= 1;
二、 连接数据库
public class Demo1 {
public static void main(String[] args) throws Exception{
//注册驱动(其实会自动注册)
Class.forName("com.mysql.jdbc.Driver");
//获得数据库的地址
String url = "jdbc:mysql://localhost:3306/myschool?useSSL=true&characterEncoding=utf8";
//连接数据库(获得连接)
Connection conn = DriverManager.getConnection(url, "root", "123456");
//预编译
PreparedStatement pstat = conn.prepareStatement("insert into student values(?,?,?)");
//给预编译的参数赋值
pstat.setObject(1, 9);
pstat.setObject(2, "兰陵王");
pstat.setObject(3, "男");
//执行数据库操作
pstat.executeUpdate();
System.out.println("操作完成");
//关闭资源
conn.close();
pstat.close();
}
}
注意:执行增删改用的是executeUpdate()方法 查找用executeQuery()返回一个结果集
三、 编写工具类(直接上高级点的使用配置文件)
配置文件:db.properties
driver = com.mysql.jdbc.Driver
url = jdbc:mysql:///myschool?useSSL=true&characterEncoding=utf8&rewriteBatchedStament=true
user = root
password = 123456
工具类代码:
public class MyDbUtils {
private static String driver;
private static String url;
private static String user;
private static String password;
static {
try {
Properties properties = new Properties();
InputStream is = MyDbUtils.class.getClassLoader().getResourceAsStream("db.properties");
properties.load(is);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//获得数据库连接
public static Connection getConnection(){
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//关闭所有连接
public static void closeAll(Connection conn, Statement stat, ResultSet rs) {
try {
if (conn != null) {
conn.close();
}
if (stat != null) {
stat.close();
}
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("关闭发生异常");
}
}
//执行 增 删 改 操作------
public static int execute_Update(String sql, Object[] params) {
Connection conn = null;
PreparedStatement pstat = null;
conn= getConnection();
try {
pstat= conn.prepareStatement(sql);
if (params!=null) {
for (int i = 0; i < params.length; i++) {
//将参数给SQL语句赋值
pstat.setObject(i + 1, params[i]);
}
}
return pstat.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(conn,pstat,null);
}
return -1;
}
// 执行查询操作 返回一个结果集
public static <T> List<T> find(String sql, Object[] params, Class<T> clazz) {
List<T> list = new ArrayList<>();
Connection conn = null;
PreparedStatement pstat = null;
ResultSet rs = null;
T t = null;
try {
conn = getConnection();
pstat = conn.prepareStatement(sql);
if (params!=null) {
for (int i = 0; i < params.length; i++) {
pstat.setObject(i + 1, params[i]);
}
}
rs = pstat.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
while (rs.next()) {
//一共有多少个字段(从1开始)
t = clazz.newInstance();
int count = metaData.getColumnCount();
for (int i = 1; i <= count; i++) {
//获取当前字段名
String columnLabel = metaData.getColumnLabel(i);
//获得当前字段的值
Object value = rs.getObject(columnLabel);
try {
PropertyDescriptor pd = new PropertyDescriptor(columnLabel, clazz);
Method writeMethod = pd.getWriteMethod();
if (writeMethod != null) {
writeMethod.invoke(t, value);
}
} catch (IntrospectionException e) {
continue;
}
}
list.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(conn,pstat,rs);
}
return list;
}
}
测试代码:
先编写一个学生类 ------->再在数据库里建立一个student表-------->开始测试:
~~~学生类
//学生类
public class Student {
private Integer id;
private String name;
private String gender;
public Student() {
}
public Student(Integer id, String name, String gender) {
this.id = id;
this.name = name;
this.gender = gender;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
'}';
}
}
~~~在数据库中建立student表
CREATE TABLE `student` (
`id` int(20) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`gender` varchar(10) DEFAULT NULL
);
~~~开始测试
public class MyDbUtilsTest {
@Test
public void test1() {
Object[] params = {2, "甄姬", "女"};
String sql1 = "insert into student values(?,?,?)";
String sql2 = "select * from student ";
int i = MyDbUtils.execute_Update(sql1, params);
System.out.println(i);
List<Student> students = MyDbUtils.find(sql2, null, Student.class);
for (Student student : students) {
System.out.println(student);
}
}
}
四、批处理操作(和事务一起)
public class PiChuLi {
//事务和批处理
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstat = null;
long star = System.currentTimeMillis();
try {
conn = MyDbUtils.getConnection();
conn.setAutoCommit(false);
pstat = conn.prepareStatement("insert into student values(?,?,?);");
for (int i = 1; i <= 100000; i++) {
pstat.setObject(1, 999);
pstat.setObject(2, "鲁班七号");
pstat.setObject(3, "男");
pstat.addBatch();
if (i % 1000 == 0) {
int[] ints = pstat.executeBatch();
System.out.println("长度:"+ints.length);
pstat.clearBatch();
}
}
int[] ints = pstat.executeBatch();
System.out.println(ints.length);
conn.commit();
long end = System.currentTimeMillis();
System.out.println("总用时:"+(end-star));
} catch (SQLException e) {
e.printStackTrace();
} finally {
MyDbUtils.closeAll(conn,pstat,null);
}
}
}
五、数据库连接池(以阿里的为例)
~~~配置文件 druid.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool?useSSL=true&characterEncoding=utf8
username=root
password=123456
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最小空闲连接 -->
minIdle=50
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=5000
~~~工具类代码
public class MyDbUtils_Druid {
private static DruidDataSource dataSource;
static {
try {
Properties properties = new Properties();
InputStream is = MyDbUtils_Druid.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
dataSource =(DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
System.out.println("注册驱动失败");
}
}
public static DataSource getDataSource() {
return dataSource;
}
}
~~~测试代码
public class MyDbUtils_Druid_Test {
@Test
public void test1() {
try {
Object[] params = {2, "甄姬", "女"};
String sql1 = "insert into student values(?,?,?)";
String sql2 = "select * from student ";
QueryRunner qr = new QueryRunner(MyDbUtils_Druid.getDataSource());
int i = qr.update(sql1, params);
List<Student> students = qr.query(sql2, new BeanListHandler<>(Student.class));
System.out.println(i);
for (Student student : students) {
System.out.println(student);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
~~~DBUtils三个常用的类
1--ScalarHandler类:适合获取一行一列数据。
2--BeanHandler类:实现类,把记录转成对象。
3-BeanListHandler类:实现类,把记录转化成List,使记录为JavaBean类型的对象
~~~注意事项,在数据库连接这一块,一定要记得导包