1,导入相关依赖,即commons-dbutils-1.8.1.jar
druid-1.2.20.jar
2.创建配置文件db_druid.properties
driverClassname=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/booksystem?characterEncoding=utf8&useSSL=false
username=root
password=123456
3.创建数据源,我用的是阿里的Druid数据源
InputStream is = DBUtils.class.getClassLoader().getResourceAsStream("db_druid.properties");
Properties properties = new Properties();
properties.load(is);
is.close();
DruidDataSource druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
4.创建数据库连接
Connection connection=druidDataSource.getConnection();
5.创建执行对象,用来执行相应的sql语句
//无参构造,后面调用queryRunner .query()方法时必须传入connection
QueryRunner queryRunner = new QueryRunner();
//有参构造,传入数据源
QueryRunner queryRunner = new QueryRunner(druidDataSource);
6.执行增删改查操作
//查询所有
List<Book> books = queryRunner.query(connection, sql, new BeanListHandler<>(Book.class));
//条件查询
List<Book> books = queryRunner.query(sql, new BeanListHandler<>(Book.class), "%"+t+"%");
//id主键查询
book= queryRunner.query(sql, new BeanHandler<>(Book.class),id);
//增加数据,必须要connection
Object[] params={book.getTitle(),book.getAuthor(),new java.sql.Date(book.getPublicDate().getTime()),book.getPublisher(),book.getIsbn(),book.getPrice(),book.getPicture(),book.getCid()};
//调用 queryRunner.update(connection,sql, params);必须要connection,修改也是一样
int i = queryRunner.update(connection,sql, params);
//修改数据,必须要connection
Object[] params={book.getTitle(),book.getAuthor(),new java.sql.Date(book.getPublicDate().getTime()),book.getPublisher(),book.getIsbn(),book.getPrice(),book.getPicture(),book.getCid(),book.getId()};
int i = queryRunner.update(connection,sql, params);
//删除数据
Object[] params={....}
int i = queryRunner.update(sql, params);