package dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.junit.Test;
import bean.User;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;
import util.ConnectionUtil;
import dao.UserDao;
public class UserDaoImpl implements UserDao{
/**
* 查看用户信息
*/
@Override
public void searchUserInfo() {
ConnectionUtil.getConnection();
}
/**
* 事务
*/
@Test
public void testTX() throws SQLException{
Connection connection =null;
try {
connection =(Connection) ConnectionUtil.getConnection();
try {
String sql = "delete from tb1 where name1 =?";
try {
ConnectionUtil.updateTX(connection, sql, "t109");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println(12/0);//出错,
String sql1 ="insert into tb1(name1) values (?)";
try {
ConnectionUtil.updateTX(connection, sql1, "12");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (Exception e) {
System.out.println("之前数据回滚");
ConnectionUtil.rollback(connection);
}
ConnectionUtil.commit(connection);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnectionUtil.close(connection, null, null);
}
}
/**
* 插入多行数据
*/
@Test
public void test1(){
Connection connection = (Connection) ConnectionUtil.getConnection();
// String sql = "insert into tb1(name1) values (?)";
String sql = "delete from tb1 where name1 =?";
for (int i = 1; i < 100; i++) {
try {
ConnectionUtil.update(sql, "t"+i);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Test
public void test4(){
String sql = "select name1 name ,id id from tb1 where id<? ";
List<User> list= ConnectionUtil.searchList(User.class, sql, 19);
for (User user2 : list) {
System.out.println(user2.toString());
}
}
@Test
public void test3(){
String sql1 = "select name1 name,id id from tb1 where id<? ";
User user =ConnectionUtil.search(User.class, sql1, 105);
System.out.println(user.toString());
}
@Test
public void test2(){
User user =new User();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet =null;
try {
String sql1 = "select * from tb1 where id<? ";
//获取连接
connection = (Connection) ConnectionUtil.getConnection();
//获取预处理
preparedStatement =(PreparedStatement) connection.prepareStatement(sql1);
//补充占位符
preparedStatement.setObject(1, 100);
//执行
resultSet = preparedStatement.executeQuery();
//获取元数据
ResultSetMetaData data = (ResultSetMetaData) resultSet.getMetaData();
//获取列数
int num = data.getColumnCount();
System.out.println(num+"列");
//遍历结果集
while (resultSet.next()) {
//封装数据
Object id = resultSet.getObject(1);
Object name = resultSet.getObject(2);
user.setId((int)id);
user.setName((String)name);
System.out.println(user.toString());
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//关闭资源
ConnectionUtil.close(connection,preparedStatement, resultSet);
}
}
public static void main(String[] args) throws Exception {
User user =new User();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet =null;
try {
String sql1 = "select * from tb1 where id<? ";
//获取连接
connection = (Connection) ConnectionUtil.getConnection();
//获取预处理
preparedStatement =(PreparedStatement) connection.prepareStatement(sql1);
//补充占位符
preparedStatement.setObject(1, 100);
//执行
resultSet = preparedStatement.executeQuery();
//遍历结果集
while (resultSet.next()) {
//封装数据
Object id = resultSet.getObject(1);
Object name = resultSet.getObject(2);
user.setId((int)id);
user.setName((String)name);
System.out.println(user.toString());
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//关闭资源
ConnectionUtil.close(connection,preparedStatement,resultSet );
}
}
}