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();
}
}
}
}
}
}
第一步:创建实体类(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();
}
}
}
}
}
}