Java,Scala:JDBCUtil,MySqlUtil,PhoenixJDBC
pom.xml添加依赖
< dependency>
< groupId> mysql< / groupId>
< artifactId> mysql- connector- java< / artifactId>
< version> 5.1 .45 < / version>
< / dependency>
Java:方式一(亲测实用)
import java. sql. *;
public class JDBCUtil {
private static final String DBDRIVER = "com.mysql.jdbc.Driver" ;
private static final String DBURL = "jdbc:mysql://(ip):3306/stu?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai" ;
private static final String DBUSER = "root" ;
private static final String DBPASSWORD = "xxxxxx" ;
private static Connection conn = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null;
public static Connection getConnection ( ) {
try {
Class. forName ( DBDRIVER) ;
conn = DriverManager. getConnection ( DBURL, DBUSER, DBPASSWORD) ;
System. out. println ( "成功加载SQL Server驱动程序" ) ;
} catch ( ClassNotFoundException e) {
System. out. println ( "找不到SQL Server驱动程序" ) ;
e. printStackTrace ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
return conn;
}
public static ResultSet select ( String sql) throws Exception {
try {
ps = ( PreparedStatement) conn. prepareStatement ( sql) ;
rs = ps. executeQuery ( ) ;
return rs;
} catch ( SQLException sqle) {
throw new SQLException ( "select data Exception: "
+ sqle. getMessage ( ) ) ;
} catch ( Exception e) {
throw new Exception ( "System error: " + e. getMessage ( ) ) ;
}
}
public static void update ( String sql) throws Exception {
try {
conn = getConnection ( ) ;
ps = ( PreparedStatement) conn. prepareStatement ( sql) ;
ps. executeUpdate ( ) ;
} catch ( SQLException sqle) {
throw new SQLException ( "insert data Exception: "
+ sqle. getMessage ( ) ) ;
} finally {
try {
if ( ps != null) {
ps. close ( ) ;
}
} catch ( Exception e) {
throw new Exception ( "ps close exception: " + e. getMessage ( ) ) ;
}
try {
if ( conn != null) {
conn. close ( ) ;
}
} catch ( Exception e) {
throw new Exception ( "conn close exception: " + e. getMessage ( ) ) ;
}
}
}
public static void closeAll ( ) {
try {
rs. close ( ) ;
ps. close ( ) ;
conn. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
方式二:Scala
import java. sql. { Connection, DriverManager }
object ScalaJdbcConnectSelect extends App {
val url = "jdbc:mysql://localhost:3306/cgjr?useUnicode=true&characterEncoding=utf-8&useSSL=false"
val driver = "com.mysql.jdbc.Driver"
val username = "root"
val password = "12345"
var connection: Connection = ""
try {
Class. forName ( driver)
connection = DriverManager. getConnection ( url, username, password)
val statement = connection. createStatement
val rs = statement. executeQuery ( "SELECT name, num FROM persons" )
while ( rs. next) {
val name = rs. getString ( "name" )
val num = rs. getString ( "num" )
println ( "name = %s, num = %s" . format ( name, num) )
}
println ( "查询数据完成!" )
val rs2 = statement. executeUpdate ( "INSERT INTO `persons` (`name`, `num`) VALUES ('徐志摩', '22')" )
println ( "插入数据完成" )
val rs3 = statement. executeUpdate ( "UPDATE persons set num=55 WHERE `name`="徐志摩"" )
println ( "更新数据完成!" )
val rs4 = statement. executeUpdate ( "delete from persons WHERE `name`="徐志摩"" )
println ( "删除数据完成!" )
val rs5 = statement. executeUpdate ( "call add_student(3)" )
println ( "调用存储过程完成!" )
} catch {
case e: Exception = > e. printStackTrace
}
connection. close
}
方式三:Java
import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. PreparedStatement;
import java. sql. ResultSet;
import java. sql. SQLException;
import java. util. List;
import org. apache. log4j. Logger;
public class DBMysqlUtil {
private Connection conn = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
private String dbDriver = null;
private String dbConnectionURL = null;
private String dbUsername = null;
private String dbPassword = null;
private PropUtil PropUtil= null;
private Logger logger = Logger. getLogger ( DBMysqlUtil. class ) ;
public DBMysqlUtil ( ) {
PropUtil = new PropUtil ( "config/db.properties" ) ;
dbDriver = PropUtil. get ( "Driver" ) ;
dbConnectionURL = PropUtil. get ( "ConnectionURL" ) ;
dbUsername = PropUtil. get ( "Username" ) ;
dbPassword = PropUtil. get ( "Password" ) ;
}
public DBMysqlUtil ( String dbDriver, String dbConnectionURL, String dbUsername, String dbPassword) {
this . dbDriver = dbDriver;
this . dbConnectionURL = dbConnectionURL;
this . dbUsername = dbUsername;
this . dbPassword = dbPassword;
}
private Connection getConnection ( ) {
System. out. println ( "连接地址:" + dbConnectionURL) ;
System. out. println ( "用户名:" + dbUsername) ;
System. out. println ( "密码:" + dbPassword) ;
try {
Class. forName ( dbDriver) ;
conn = DriverManager. getConnection ( dbConnectionURL, dbUsername,
dbPassword) ;
logger. info ( "数据库连接成功" ) ;
} catch ( Exception e) {
logger. error ( "Error: DbUtil.getConnection() 获得数据库链接失败.
链接类型:"
+ dbDriver + "
链接URL:" + dbConnectionURL + "
链接用户:"
+ dbUsername + "
链接密码:" + dbPassword, e) ;
}
return conn;
}
public ResultSet select ( String sql) {
logger. info ( "Exec select sql:" + sql) ;
try {
conn = getConnection ( ) ;
ps = conn. prepareStatement ( sql) ;
rs = ps. executeQuery ( sql) ;
} catch ( SQLException e) {
logger. error ( "查询数据异常:" + e. getMessage ( ) ) ;
}
return rs;
}
public int getRecordCount ( String sql) {
logger. info ( "Exec getRecordCount sql:" + sql) ;
int counter = 0 ;
try {
conn = getConnection ( ) ;
ps = conn. prepareStatement ( sql) ;
rs = ps. executeQuery ( sql) ;
while ( rs. next ( ) ) {
counter++ ;
}
} catch ( SQLException e) {
logger. error ( "执行DbUtil.getRecordCount()方法发生异常,异常信息:" , e) ;
} finally {
close ( ) ;
}
System. out. println ( "counter总数:" + counter) ;
return counter;
}
public int executeupdate ( String sql) throws Exception {
logger. info ( "Exec update sql:" + sql) ;
int num = 0 ;
try {
conn = getConnection ( ) ;
ps = conn. prepareStatement ( sql) ;
num = ps. executeUpdate ( ) ;
} catch ( SQLException sqle) {
logger. error ( "insert/update/delete data Exception: " +
sqle. getMessage ( ) ) ;
} finally {
close ( ) ;
}
System. out. println ( "影响条数:" + num) ;
return num;
}
public int executeBatch ( List< String> sqlList) {
int result = 0 ;
for ( String sql : sqlList) {
try {
result += executeupdate ( sql) ;
} catch ( Exception e) {
System. out. println ( "查询异常:" + e. getMessage ( ) ) ;
}
}
System. out. println ( "executeBatch Result:" + result) ;
return result;
}
public void close ( ) {
try {
if ( rs != null) {
rs. close ( ) ;
}
if ( ps != null) {
ps. close ( ) ;
}
if ( conn != null) {
conn. close ( ) ;
}
logger. info ( "关闭数据库连接成功" ) ;
} catch ( Exception e) {
logger. error ( "执行DbUtil.close()方法发生异常,异常信息:" , e) ;
}
}
}
PhoenixJDBCUtils
import java. sql. *;
public class PhoenixJDBCUtils {
private static String driverClassName;
private static String URL;
private static String username;
private static String password;
private static boolean autoCommit;
private static Connection conn;
static {
config ( ) ;
}
private static void config ( ) {
driverClassName = "org.apache.phoenix.jdbc.PhoenixDriver" ;
URL = "jdbc:phoenix:slave1,slave2,slave3:2181/hbase" ;
username = "" ;
password = "" ;
autoCommit = true ;
}
private static boolean load ( ) {
try {
Class. forName ( driverClassName) ;
return true ;
} catch ( ClassNotFoundException e) {
System. out. println ( "驱动类 " + driverClassName + " 加载失败" ) ;
}
return false ;
}
public static Connection connect ( ) {
load ( ) ;
try {
conn = DriverManager. getConnection ( URL, username, password) ;
} catch ( SQLException e) {
System. out. println ( "建立数据库连接失败 , " + e. getMessage ( ) ) ;
}
return conn;
}
public static void transaction ( ) {
try {
conn. setAutoCommit ( autoCommit) ;
} catch ( SQLException e) {
System. out. println ( "设置事务的提交方式为 : " + ( autoCommit ? "自动提交" : "手动提交" ) + " 时失败: " + e. getMessage ( ) ) ;
}
}
public static Statement statement ( ) {
Statement st = null;
connect ( ) ;
transaction ( ) ;
try {
st = conn. createStatement ( ) ;
} catch ( SQLException e) {
System. out. println ( "创建 Statement 对象失败: " + e. getMessage ( ) ) ;
}
return st;
}
private static PreparedStatement prepare ( String SQL, boolean autoGeneratedKeys) {
PreparedStatement ps = null;
connect ( ) ;
transaction ( ) ;
try {
if ( autoGeneratedKeys) {
ps = conn. prepareStatement ( SQL, Statement. RETURN_GENERATED_KEYS) ;
} else {
ps = conn. prepareStatement ( SQL) ;
}
} catch ( SQLException e) {
System. out. println ( "创建 PreparedStatement 对象失败: " + e. getMessage ( ) ) ;
}
return ps;
}
public static ResultSet query ( String SQL, Object. . . params) {
if ( SQL == null || ! SQL. trim ( ) . toLowerCase ( ) . startsWith ( "select" ) ) {
throw new RuntimeException ( "你的SQL语句为空或不是查询语句" ) ;
}
ResultSet rs = null;
if ( params. length > 0 ) {
PreparedStatement ps = prepare ( SQL, false ) ;
try {
for ( int i = 0 ; i < params. length; i++ ) {
ps. setObject ( i + 1 , params[ i] ) ;
}
rs = ps. executeQuery ( ) ;
} catch ( SQLException e) {
System. out. println ( "执行SQL失败: " + e. getMessage ( ) ) ;
}
} else {
Statement st = statement ( ) ;
try {
rs = st. executeQuery ( SQL) ;
} catch ( SQLException e) {
System. out. println ( "执行SQL失败: " + e. getMessage ( ) ) ;
}
}
return rs;
}
public static void release ( Object cloaseable) {
if ( cloaseable != null) {
if ( cloaseable instanceof ResultSet ) {
ResultSet rs = ( ResultSet) cloaseable;
try {
rs. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( cloaseable instanceof Statement ) {
Statement st = ( Statement) cloaseable;
try {
st. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( cloaseable instanceof Connection ) {
Connection c = ( Connection) cloaseable;
try {
c. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
}
}