java代码连接jdbc_纯JDBC代码连接实例 + 完整代码

JDBC连接数据库实例

1、 数据库表

1.1、创建表

CREATE TABLE `users`(

`uid` INT NOT NULL AUTO_INCREMENT COMMENT '用户id',

`uname` VARCHAR(20) NOT NULL COMMENT '用户名',

`upass` VARCHAR(20) NOT NULL COMMENT '用户密码',

PRIMARY KEY (`uid`)

)ENGINE = INNODB DEFAULT CHARSET = utf8;

1.2、插入数据

INSERT INTO `users` (`uname`,`upass`) VALUES

('zhangwuji','123456'),

('zhaomin','123456'),

('zhouzhiruo','123456'),

('xiaozhao','123456');

1.3、查看表

​

2、 java部分

2.1根据数据库表中的字段创建pojo对象

package com.cugb.pojo;

import lombok.ToString;

/**

* @author huangjian

* @data 2020/7/6 11:11

*/

public class Users {

//用户id

private int uid;

//用户名

private String uname;

//密码

private String upass;

public Users() {

}

public Users(int uid, String uname, String upass) {

this.uid = uid;

this.uname = uname;

this.upass = upass;

}

public int getUid() {

return uid;

}

public void setUid(int uid) {

this.uid = uid;

}

public String getUname() {

return uname;

}

public void setUname(String uname) {

this.uname = uname;

}

public String getUpass() {

return upass;

}

public void setUpass(String upass) {

this.upass = upass;

}

@Override

public String toString() {

return "uid:" + uid + "\t" + "uname:" + uname+ "\t" +"upass:" + upass;

}

}

2.2、创建连接jdbc的工具类

package com.cugb.utils;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.mysql.jdbc.Driver;

public class DBUTils {

private static String url = "jdbc:mysql://localhost:3306/mybatis?characterEncoding=UTF8";

private static String user = "root";

private static String pass = "123456";

static {

try {

new Driver();

} catch (SQLException e) {

e.printStackTrace();

}

}

//连接数据库

public static Connection getConnection() {

Connection conn = null;

try {

conn = DriverManager.getConnection(url,user,pass);

} catch (Exception e) {

e.printStackTrace();

}

return conn;

}

//查询

public static Object[] Query(String sql) {

Connection conn = getConnection();

PreparedStatement pst =null;

ResultSet rs = null;

try {

pst = conn.prepareStatement(sql);

rs = pst.executeQuery();

} catch (SQLException e) {

e.printStackTrace();

}

Object[] obj = new Object[]{rs,pst,conn};

return obj;

}

//增删改

public static boolean DML(String sql, Object[] object) {

Connection conn = getConnection();

PreparedStatement pst = null;

Boolean b = false;

try {

pst = conn.prepareStatement(sql);

for(int i=0; i

pst.setObject(i+1, object[i]);

}

b = pst.executeUpdate()==1;

} catch (SQLException e) {

e.printStackTrace();

}

close(conn,pst,null);

return b;

}

//关闭资源

public static void close(Connection conn,PreparedStatement pst,ResultSet rs){

try{

if(rs != null && !rs.isClosed()) {

rs.close();

}

if(pst != null && !pst.isClosed()){

pst.close();

}

if(conn != null && !conn.isClosed()){

conn.close();

}

}catch(Exception e){

e.printStackTrace();

}

}

}

2.3、dao层创建接口，并添加增、删、改、查等方法

import com.cugb.pojo.Users;

import java.util.ArrayList;

public interface UsersDao {

//查询用户

public ArrayList queryUsers(String sql);

//添加用户

public boolean addUser(Users user);

//修改用户

public boolean changeUser(String sql,Object[] obj);

}

dao层创建接口实现类，并重写接口的方法

package com.cugb.dao;

import com.cugb.pojo.Users;

import com.cugb.utils.DBUTils;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

/**

* @author huangjian

* @data 2020/7/6 11:37

*/

public class UsersDaoImpl implements UsersDao {

//查询用户

public ArrayList queryUsers(String sql){

Object[] obj = DBUTils.Query(sql);

ResultSet rs = (ResultSet)obj[0];

ArrayList list = new ArrayList();

try {

while(rs.next()){

int uid = rs.getInt(1);

String uname = rs.getString(2);

String upass = rs.getString(3);

Users user = new Users(uid,uname,upass);

list.add(user);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

PreparedStatement pst = (PreparedStatement)obj[1];

Connection conn = (Connection)obj[2];

DBUTils.close(conn, pst, rs);

return list;

}

//添加用户

public boolean addUser(Users user) {

String sql = "insert into Users (uname,upass values (?,?)";

Object[] obj = new Object[]{user.getUname(),user.getUpass()};

boolean flag = DBUTils.DML(sql, obj);

return flag;

};

//修改用户

public boolean changeUser(String sql,Object[] obj) {

boolean flag = DBUTils.DML(sql, obj);

return flag;

}

}

2.4、测试类查询功能

package com.cugb.test;

import com.cugb.dao.UsersDaoImpl;

import com.cugb.pojo.Users;

import java.util.ArrayList;

/**

* @author huangjian

* @data 2020/7/6 11:49

*/

public class UsersTest {

public static void main(String[] args) {

UsersDaoImpl usersDao = new UsersDaoImpl();

String sql = "select * from users";

ArrayList list = usersDao.queryUsers(sql);

for (Users users : list) {

System.out.println(users);

}

}

}

3、结果

uid:1uname:zhangwujiupass:123456

uid:2uname:zhaominupass:123456

uid:3uname:zhouzhiruoupass:123456

uid:4uname:xiaozhaoupass:123456