dbutils(初学者自学经历)

个人学习经历  写写博客  就当做笔记了吧  

接触Java一段时间了   慢慢的了解它吧   

个人一开始最讨厌数据库    最初觉得语句复杂   记录下来

//jdbc链接

public class TestJdbc {
private static final String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
private static final String username = "root";
private static final String password = "250702";
private static final String driver = "com.mysql.jdbc.Driver";

static {
try {

Class.forName(driver);      //数据库驱动
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}



/**
* 测试连接数据库1
*/
@Test
public void m01() throws SQLException{
Connection conn = DriverManager.getConnection(url, username, password);
DatabaseMetaData dmd = conn.getMetaData();     //获取数据库的元信息, 数据库版本, 数据库的名字, 数据库支持哪些数据类型
String name = dmd.getDatabaseProductName();
String version = dmd.getDatabaseProductVersion();
System.out.println("name = " + name + " version = " + version);
}

/**
*测试是否连接成功 
*/
@Test
public void m02() throws SQLException {
String sql = "select * from student where sno=? and sname=?";
Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement ps = conn.prepareStatement(sql); 
//PreparedStatement它是Statement的一个子接口,也可以用于执行SQL语句,防止SQL语句的注入
ps.setInt(1, 001);
ps.setString(2, "张三");
ResultSet rs = ps.executeQuery();
while(rs.next()) { //判断是否有结果
int sno = rs.getInt(1);
String sname = rs.getString(2);
int tno = rs.getInt(3);
System.out.println(sno+ " " + sname + " " + tno);
}
conn.close();
}
}

//C3P0连接池

public class TestC3P0 {
private static DataSource ds; //通过数据源来获取连接对象
static {
ds = new ComboPooledDataSource();
}

@Test
public void m01() throws SQLException {
Connection conn = ds.getConnection();
DatabaseMetaData dmd = conn.getMetaData();
System.out.println(dmd.getDatabaseProductName()+""+dmd.getDatabaseProductVersion());
}
}

//   利用dbutils来进行数据库的增删改查

public class TestDbutils {
private static DataSource ds; //通过数据源来获取连接对象
static {
ds = new ComboPooledDataSource();
}

/**
* 用dbutils 查找表
*/
@Test
public void m01() throws SQLException{
Connection conn = ds.getConnection();
String sql = "select * from student";
QueryRunner runner = new QueryRunner();     //初始化要执行的SQL语句对象,在Dbutils组件中是QueryRunner
List<Map<String, Object>> list = runner.query(conn, sql, new MapListHandler());
System.out.println(list);
}
/**
* m01与m02作对比
*/
@Test
public void m02() throws SQLException {
Connection conn = ds.getConnection();
String sql = "select * from student";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
List<Map<String, Object>> list = new ArrayList<>();
while(rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("sid", rs.getInt(1));
map.put("sname", rs.getString(2));
map.put("tid", rs.getInt(3));
list.add(map);
}
System.out.println(list);
}
@Test
public void m03() throws SQLException{
Connection conn = ds.getConnection();
String sql = "select * from student";
QueryRunner runner = new QueryRunner();
List<Map<String, Object>> list = runner.query(conn, sql, new MapListHandler());
System.out.println(list);
}
/**
* Dbutils的增删改查
* @throws SQLException 
*/

//增
@Test
public void m04() throws SQLException{
Connection conn = ds.getConnection();
QueryRunner runner = new QueryRunner();
String sql = "insert into student values(?,?,?,?)";
runner.update(conn, sql, new Object[]{6,"阳哥",19,"男"});
m01();
}
//删
@Test
public void m05() throws SQLException{
Connection conn = ds.getConnection();
QueryRunner runner = new QueryRunner();
String sql = "delete from student where sno=?";
runner.update(conn, sql, new Object[]{6});
m01();
}
//改
@Test
public void m06() throws SQLException{
Connection conn = ds.getConnection();
QueryRunner runner = new QueryRunner();
String sql = "update student set ssex=? where sno = ?";
runner.update(conn, sql, new Object[]{"女",5});
m01();
}
}