JDBC基础连接（代码）

JDBC:java DataBase Connectivity（java数据库连接）。
第一步：创建实体类(User)。代码如下：
package com.jkx.po;


public class User {
private int userId;
private String username;
private String password;
private int sex;
private int flag;
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public int getFlag() {
return flag;
}
public void setFlag(int flag) {
this.flag = flag;
}

}
第二步：创建数据库操作层dao包。
package com.jkx.dao;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;


import com.jkx.po.User;
import com.jkx.util.DBConnectionUtil;


public class UserDao {

/*
public UserDao(){}
private static UserDao userDao = new UserDao();
private static User getInstance(){
  return userDao;
}
*/


private String jdbcDriver="com.mysql.jdbc.Driver";
private String jdbcURL="jdbc:mysql://localhost:3306/jdbc?characterEncoding=UTF-8";
private String jdbcUser="root";
private String jdbcPassword="123456";


//增加数据
public boolean SaveUser(User user){
boolean flag=false;
Connection conn=null;
Statement st=null;
try {
Class.forName("jdbcDriver");
conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);

String sql="insert into user(username,password) values('"+user.getUsername()+"','"+user.getPassword()+"')";

st=conn.createStatement();
int rows = st.executeUpdate(sql);

if(rows>0)
{
flag=true;
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();

}finally{
if(st!=null)
{
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}

return flag;
}

//删除数据
public boolean deleteUser(int userId){
boolean flag=false;
Connection conn = null;
Statement st = null;

try {
Class.forName("jdbcDriver");
conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);

String sql = "delete from user where userId="+userId;

st=conn.createStatement();
int row = st.executeUpdate(sql);
if(row>0)
{
flag=true;
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(st!=null)
{
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}

return flag;
}

//修改数据
public boolean updateUserBymap(Map<String, Object> map){
boolean flag=false;
Connection conn = null;
Statement st = null;

try {
Class.forName("jdbcDriver");
conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);

String sql = "update user set password='"+(String)map.get("password")+"' where userId='"+(Integer)map.get("userId")+"'";

st=conn.createStatement();
int row = st.executeUpdate(sql);
if(row>0)
{
flag=true;
}

} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(st!=null)
{
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}

return flag;
}

//查找数据

}


第三步：创建测试类（UserDaoTest）
package com.jkx.test;


import java.util.HashMap;
import java.util.Map;


import org.junit.Test;


import com.jkx.dao.UserDao;
import com.jkx.po.User;


public class UserTest {


/**
* user测试类
*/
@Test
public void insertTest() {

UserDao userDao = new UserDao();
User user = new User();
user.setUsername("ss");
user.setPassword("1111");
if(userDao.SaveUser(user)==true)
{
System.out.println("保存成功");
}else{
System.out.println("保存失败");
}
}

@Test
public void deleteTest(){
UserDao userdao = new UserDao();
System.out.println(userdao.deleteUser(6) ? "删除成功" : "删除失败");
}

@Test
public void updateTest(){
Map<String, Object> map = new HashMap<String, Object>();
UserDao userdao = new UserDao();
map.put("userId", 1);
map.put("password", "sssss");
System.out.println(userdao.updateUserBymap(map) ? "修改成功" : "修改失败");
}


}
最后进行测试。测试成功后，对代码进行优化。


第一步：创建一个封装的工具类。代码如下：
package com.jkx.util;


import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class DBConnectionUtil {


private static String jdbcDriver="com.mysql.jdbc.Driver";
private static String jdbcURL="jdbc:mysql://localhost:3306/jdbc?characterEncoding=UTF-8";
private static String jdbcUser="root";
private static String jdbcPassword="123456";

public static Connection getConnection(){
Connection conn=null;
try {
Class.forName(jdbcDriver);
conn=DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);
} catch (ClassNotFoundException e) {


e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println(conn);
return conn;
}

//先产生的后关闭，后产生的先关闭
public static void clostAll(Statement st,Connection conn){
if(st!=null)
{
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

}
}
}
然后优化UserDao类。代码如下：
package com.jkx.dao;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;


import com.jkx.po.User;
import com.jkx.util.DBConnectionUtil;


public class UserDao {

/*
public UserDao(){}
private static UserDao userDao = new UserDao();
private static User getInstance(){
  return userDao;
}
*/

/*
private String jdbcDriver="com.mysql.jdbc.Driver";
private String jdbcURL="jdbc:mysql://localhost:3306/jdbc?characterEncoding=UTF-8";
private String jdbcUser="root";
private String jdbcPassword="123456";
*/

//增加数据
public boolean SaveUser(User user){
boolean flag=false;
Connection conn=null;
Statement st=null;
try {

conn = DBConnectionUtil.getConnection();

String sql="insert into user(username,password) values('"+user.getUsername()+"','"+user.getPassword()+"')";

st=conn.createStatement();
int rows = st.executeUpdate(sql);

if(rows>0)
{
flag=true;
}

} catch (SQLException e) {
e.printStackTrace();
}finally{
DBConnectionUtil.clostAll(st, conn);
}

return flag;
}

//删除数据
public boolean deleteUser(int userId){
boolean flag=false;
Connection conn = null;
Statement st = null;

try {
conn = DBConnectionUtil.getConnection();
String sql = "delete from user where userId="+userId;

st=conn.createStatement();
int row = st.executeUpdate(sql);
if(row>0)
{
flag=true;
}

} catch (SQLException e) {
e.printStackTrace();
}finally{
DBConnectionUtil.clostAll(st, conn);
}

return flag;
}

//修改数据
public boolean updateUserBymap(Map<String, Object> map){
boolean flag=false;
Connection conn = null;
Statement st = null;

try {
conn = DBConnectionUtil.getConnection();
String sql = "update user set password='"+(String)map.get("password")+"' where userId='"+(Integer)map.get("userId")+"'";

st=conn.createStatement();
int row = st.executeUpdate(sql);
if(row>0)
{
flag=true;
}


} catch (SQLException e) {
e.printStackTrace();
}finally{
DBConnectionUtil.clostAll(st, conn);
}

return flag;
}

//查找数据

}
最后进一步优化，使class加载驱动只加载再一次。
创建jdbc.properties,内容如下：
jdbcDriver=com.mysql.jdbc.Driver
jdbcUrl=jdbc\:mysql\://localhost\:3306/jdbc?useUnicode\=true&characterEncoding\=UTF-8
jdbcUser=root
jdbcPassword=123456


然后优化：
package com.jkx.util;


import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class DBConnectionUtil {


// private static String jdbcDriver="com.mysql.jdbc.Driver";
// private static String jdbcURL="jdbc:mysql://localhost:3306/jdbc?characterEncoding=UTF-8";
// private static String jdbcUser="root";
// private static String jdbcPassword="123456";

private static String jdbcDriver="";
private static String jdbcURL="";
private static String jdbcUser="";
private static String jdbcPassword="";

static{
InputStream is = DBConnectionUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties p = new Properties();
try {
p.load(is);
jdbcDriver=p.getProperty("jdbcDriver");
jdbcURL=p.getProperty("jdbcUrl");
jdbcUser=p.getProperty("jdbcUser");
jdbcPassword=p.getProperty("jdbcPassword");
} catch (IOException e) {
e.printStackTrace();
}
}

public static Connection getConnection(){
Connection conn=null;
try {
Class.forName(jdbcDriver);
conn=DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);
} catch (ClassNotFoundException e) {


e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println(conn);
return conn;
}

//先产生的后关闭，后产生的先关闭
public static void clostAll(Statement st,Connection conn){
if(st!=null)
{
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

}
}
}