这篇文章主要介绍了java怎么连接数据库executeUpdate()和executeQuery()的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇java怎么连接数据库executeUpdate()和executeQuery()文章都会有所收获,下面我们一起来看看吧。
//没有返回值
public void update(int count){
conn=DBUtil.getConn();
String sql="update counter set count=?";
try {
PreparedStatement ps = conn.prepareStatement(sql);
//传进去的
ps.setInt(1,count);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeConn();
}
}
//没有返回值,参数是个字符串部门名称就ok了,因为id的话是自增
public void insert(String departmentname) {
conn = ConnectionFactory.getConnection();
String sql = "insert into department (departmentname) values(?)";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, departmentname);
pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
ConnectionFactory.closeConnection();
}
}
//因为employeeid自增,所以不用设置
public void insert(Employee employee){
conn=ConnectionFactory.getConnection();
String sql="insert into employee"
+
"(employeename,username,password,phone,email,departmentid,status,role)" +
" values(?,?,?,?,?,?,?,?)";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,employee.getEmployeename());
pstmt.setString(2,employee.getUsername());
pstmt.setString(3,employee.getPassword() );
pstmt.setString(4,employee.getPhone() );
pstmt.setString(5,employee.getEmail());
pstmt.setInt(6,employee.getDepartmentid());
//注册成功后,默认为正在审核,status为0
pstmt.setString(7,"0");
//注册时,默认为员工角色,role值为2
pstmt.setString(8,"2");
pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnectionFactory.closeConnection();
}
}
//删除不用返回值
public void delete(int departmentid) {
conn = ConnectionFactory.getConnection();
String sql = "delete from department where departmentid=?;";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, departmentid);
pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
ConnectionFactory.closeConnection();
}
}
select
//返回int类型
public int select(){
int count=0;
conn=DBUtil.getConn();
String sql = "select * from counter";
try{
PreparedStatement ps = conn.PreparedStatement(sql);
ResultSet rs =ps.excuteQuery();
if(rs.next()){
count=rs.getInt("visitcount");
}
}catch{
}finally{
DBUtil.closeConn();
}
return count;
}
//返回部门集合
public List<Department> selectAll() {
conn = ConnectionFactory.getConnection();
// 新建一个集合departmentsList
List<Department> departmentsList = new ArrayList<Department>();
try {
Statement st = null;
String sql = "select * from department";
st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
Department department;
while (rs.next()) {
// 新建一个department来接收数据库的信息
department = new Department();
department.setDepartmentid(rs.getInt("departmentid"));
department.setDepartmentname(rs.getString("departmentname"));
departmentsList.add(department);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
ConnectionFactory.closeConnection();
}
// 返回集合
return departmentsList;
}
//返回员工
public List<Employee> selectAllEmployee(){
conn=ConnectionFactory.getConnection();
List<Employee> employeeslist=new ArrayList<Employee>();
Employee employee=null;
try {
PreparedStatement st=null;
//只查询已注册且未审批 且 角色是员工的
String sql="select * from employee where role='2' and status='0'";
st = conn.prepareStatement(sql);
ResultSet rs =st.executeQuery(sql);
while(rs.next()){
employee=new Employee();
employee.setEmployeeid(rs.getInt("employeeid"));
employee.setEmployeename(rs.getString("employeename"));
employee.setUsername(rs.getString("username"));
employee.setPhone(rs.getString("phone"));
employee.setEmail(rs.getString("email"));
employee.setStatus(rs.getString("status"));
employee.setDepartmentid(rs.getInt("departmentid"));
employee.setPassword(rs.getString("password"));
employee.setRole(rs.getString("role"));
employeeslist.add(employee);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
//最后总要关闭连接
ConnectionFactory.closeConnection();
}
return employeeslist;
}
public Employee selectByNamePwd(String username, String pwd) {
Employee employee = null;
try {
//创建PreparedStatement对象
PreparedStatement st = null;
//查询语句
String sql = "select * from employee where username='" + username + "' and password='" + pwd + "'";
st = conn.prepareStatement(sql);
ResultSet rs = st.executeQuery(sql);
//判断结果集有无记录,如果有:则把内容取出来,变成一个employee对象,并且返回它
if (rs.next() == true) {
employee = new Employee();
employee.setEmployeeid(rs.getInt("employeeid"));
employee.setEmployeename(rs.getString("employeename"));
employee.setUsername(rs.getString("username"));
employee.setPhone(rs.getString("phone"));
employee.setEmail(rs.getString("email"));
employee.setStatus(rs.getString("status"));
employee.setDepartmentid(rs.getInt("status"));
employee.setPassword(rs.getString("password"));
employee.setRole(rs.getString("role"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
ConnectionFactory.closeConnection();
}
return employee;
}
public Employee selectByUsername(String username){
conn=ConnectionFactory.getConnection();
Employee employee=null;
try {
PreparedStatement st=null;
String sql="select * from employee where username='"+username+"'";
st = conn.prepareStatement(sql);
ResultSet rs =st.executeQuery(sql);
if(rs.next()==true){
employee=new Employee();
employee.setEmployeeid(rs.getInt("employeeid"));
employee.setEmployeename(rs.getString("employeename"));
employee.setUsername(rs.getString("username"));
employee.setPhone(rs.getString("phone"));
employee.setEmail(rs.getString("email"));
employee.setStatus(rs.getString("status"));
employee.setDepartmentid(rs.getInt("status"));
employee.setPassword(rs.getString("password"));
employee.setRole(rs.getString("role"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
ConnectionFactory.closeConnection();
}
return employee;
}
上面有个小陷阱
如果加了
会正常执行,如果没有加,会因为字段不是字符串而报错.
结果集为空
也就是说public Employee(String user,int id, String pwd){}
和 public Employee(int id,String user,String pwd){} 是不一样的构造方法
测试main方法里,插入的数据的类型顺序决定了调用哪个构造方法.
看起来没有区别(这个不敢肯定)
会报错
应该在里面放单引号
关于“java怎么连接数据库executeUpdate()和executeQuery()”这篇文章的内容就介绍到这里,感谢各位的阅读!相信大家对“java怎么连接数据库executeUpdate()和executeQuery()”知识都有一定的了解,大家如果还想学习更多知识,欢迎关注亿速云行业资讯频道。
亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。