java访问数据库的 javaBean

最新推荐文章于 2020-04-26 18:12:19 发布

原创最新推荐文章于 2020-04-26 18:12:19 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #null #数据库连接池 #mysql #string

这是一个简单的java操作mysql数据库的例子，没有用到数据库连接池。

MysqlConnDB.java

Code:

/*
*访问mysql数据库的 javaBean
*/
package com.classes;
import java.sql.*;
import java.io.*;
import java.util.*;
public class MysqlConnDB{
public Connection conn = null;
public ResultSet rs = null;
public PreparedStatement pStmt = null;
private static String dbDriver = "com.mysql.jdbc.Driver";
private static String dbUrl = "jdbc:mysql://localhost:3306/myjavaqq?useUnicode=true&characterEncoding=utf8";
private static String dbUserName = "root";
private static String dbPwd = "isone";
public static Connection getConnection(){
Connection conn = null;
try{
MyProperties prop = new MyProperties("mysql.properties");
dbDriver = prop.getProperty("dbDriver");
String databaseName = prop.getProperty("databaseName");
dbUserName = prop.getProperty("dbUserName");
dbPwd = prop.getProperty("dbPassword");
String address = prop.getProperty("address");
String dbPort = prop.getProperty("dbPort");
dbUrl = "jdbc:mysql://" + address + ":" + dbPort + "/" + databaseName + "?useUnicode=true&characterEncoding=utf8";
//加载驱动
Class.forName(dbDriver);
//创建连接
conn = DriverManager.getConnection(dbUrl,dbUserName,dbPwd);
}
catch(Exception e){
e.printStackTrace();
}
if(conn==null){
System.err.println("警告：数据库连接失败！");
}
return conn;
}
//查询操作
public ResultSet doQuery(String sql,Vector v_parameter){
try{
conn = MysqlConnDB.getConnection();
pStmt = conn.prepareStatement(sql);
int parameterCount = v_parameter.size();
for(int i=0;i<parameterCount;i++){
try{
pStmt.setString(i+1,(String)v_parameter.get(i));
}catch(Exception ex){
pStmt.setString(i+1,v_parameter.get(i).toString());
}
}
rs = pStmt.executeQuery();
}
catch(SQLException e){
e.printStackTrace();
}
catch(Exception e){
e.printStackTrace();
}
finally{
try{
if(pStmt!=null){
pStmt.clearParameters();
}
}catch(SQLException sqlex){
}
}
return rs;
}
//更新插入删除操作
public int doUpdate(String sql,Vector v_parameter){
int result = 0;
try{
conn = MysqlConnDB.getConnection();
pStmt = conn.prepareStatement(sql);
int parameterCount = v_parameter.size();
for(int i=0;i<parameterCount;i++){
try{
pStmt.setString(i+1,(String)v_parameter.get(i));
}catch(Exception ex){
pStmt.setString(i+1,v_parameter.get(i).toString());
}
}
result = pStmt.executeUpdate();
}
catch(SQLException e){
e.printStackTrace();
result = 0;
}
catch(Exception e){
e.printStackTrace();
}
finally{
try{
if(pStmt!=null){
pStmt.clearParameters();
}
}catch(SQLException sqlex){
}
}
return result;
}
//关闭操作
public void closeConnection(){
try{
if(rs!=null){
rs.close();
}
}catch(Exception e){
e.printStackTrace();
}
try{
if(pStmt != null){
pStmt.close();
}
}catch(Exception e){
e.printStackTrace();
}
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}

读取配置的类MyProperties.java

Code:

package com.classes;
import java.util.Properties;
import java.io.*;
public class MyProperties{
Properties prop = null;
public MyProperties(){
}
public MyProperties(String pfileName){
try{
prop = new Properties();
File file = new File(pfileName);
FileInputStream finput = new FileInputStream(file);
prop.load(finput);
finput.close();
}
catch(FileNotFoundException fe){
fe.printStackTrace();
}
catch(IOException ie){
ie.printStackTrace();
}
}
public String getProperty(String key){
//return new String(prop.getProperty(key).getBytes("Latin1"), "GBK");
return prop.getProperty(key).trim();
}
}

配置文件mysql.properties

Code:

#数据库配置
dbDriver = com.mysql.jdbc.Driver
databaseName = myjavaqq
dbUserName = root
dbPassword = isone
address = localhost
dbPort = 3306

测试类ConnTest.java

Code:

package com.classes;
import java.util.*;
import java.sql.*;
public class ConnTest{
//写入数据
MysqlConnDB connDB = null;
Vector v_parameter = new Vector();
String sqlString = "";
int rowCount = -1;
public ConnTest(){
connDB = new MysqlConnDB();
}
//创建表
private void createTable(){
sqlString = "CREATE TABLE `t_user` (`i_Id` int(10) unsigned NOT NULL auto_increment,`i_age` int(3) NOT NULL,";
sqlString = sqlString +"`c_userName` char(10) NOT NULL, PRIMARY KEY (`i_Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
v_parameter.clear();
rowCount = connDB.doUpdate(sqlString,v_parameter);
if(rowCount>0){
System.out.println("创建表成功");
}
}
//插入数据
private void createNewUser(int age,String name){
sqlString = "INSERT INTO t_user VALUES(?,?,?);";
v_parameter.clear();
v_parameter.add(null);
v_parameter.add(age);
v_parameter.add(name);
rowCount = connDB.doUpdate(sqlString,v_parameter);
if(rowCount>0){
System.out.println("创建用户成功");
}
}
//查询数据
private int selectUser(String name)throws Exception{
sqlString = "SELECT * FROM t_user WHERE c_userName=?;";
v_parameter.clear();
v_parameter.add(name);
ResultSet rs = connDB.doQuery(sqlString,v_parameter);
rs.next();
return Integer.parseInt(rs.getString("i_Id"));
}
//删除数据
private void deleteUser(String name)throws Exception{
int id = selectUser(name);
sqlString = "DELETE FROM t_user WHERE i_Id=?;";
v_parameter.clear();
v_parameter.add(id);
rowCount = connDB.doUpdate(sqlString,v_parameter);
if(rowCount>0){
System.out.println("删除成功");
}
}
//更新数据
private void updataUser(int id,int age,String name){
sqlString = "UPDATE t_user SET i_age=?,c_userName=? WHERE i_Id=?";
v_parameter.clear();
v_parameter.add(age);
v_parameter.add(name);
v_parameter.add(id);
rowCount = connDB.doUpdate(sqlString,v_parameter);
if(rowCount>0){
System.out.println("更新成功");
}
}
public static void main(String[] args){
try{
ConnTest test = new ConnTest();
test.createTable();
test.createNewUser(18,"杨sheep冠");
int id = test.selectUser("杨sheep冠");
test.updataUser(id,23,"杨sheep");
test.deleteUser("杨sheep");
test.createNewUser(22,"yang");
}
catch(Exception e){
e.printStackTrace();
}
}
}