工具：mysql 5.6.19,mysql-connector-java-5.1.48.jar,android stuido&android studio自带模拟器
1.在mysql数据库中新建表customer,并创建相关字段
SQL命令如下
create table customer(username varchar(20),password varchar(20),email varchar(40));
alter table customer add id int(5) auto_increment primary key;
mysql> insert into customer(username,password,email) values('张三','123456','zhangsan@5429.com');
insert into customer(username,password,email) values('李四','234567','lisi@5429.com');
insert into customer(username,password,email) values('王五','345678','wangwu@5429.com');
insert into customer(username,password,email) values('陈六','456789','chenliu@5429.com');
select * from customer;
2.JDBC连接数据库
------>页面布局
------>设置按钮点击监听
------>导入mysql-connector-java-5.1.48.jar包，搜索下载即可
复制粘贴到lib文件下，右键，add as library...
------>JDBC连接数据库
（1），添加网络许可<uses-permission android:name="android.permission.INTERNET"/>
（2）Util类代码(待优化)
package com.vocus.jdbctest import java.lang.Exception
import java.sql.Connection
import java.sql.DriverManager class Util {
var conn:Connection?=null
val driver="com.mysql.jdbc.Drvier"
val url="jdbc:mysql://10.0.2.2:3306/test"
val user="root" //root用户
val psd="rs123456" //自己的密码
fun connection():Connection?{
try{
Class.forName(driver).newInstance()
}catch(e:Exception){
e.printStackTrace()
}
try{
conn=DriverManager.getConnection(url,user,psd)
}catch (e:Exception){
e.printStackTrace()
}
return conn
}
}
（3）针对数据库表customer的查询(待优化)
class Customer {
open val username:String?=null //权宜之计
open val password:String?=null
open val email:String?=null
override fun toString(): String {
return "Customer(username=$username, password=$password, email=$email)"
}
}
package com.vocus.jdbctest
import java.lang.Exception
class CustomerForQuery {
fun query(sql: String, vararg args: Any): Customer?{//sql语句如select username from customer where id=?,args不确定参数
try {
// Thread({
var conn = Util().connection()
var ps = conn!!.prepareStatement(sql)
for (i in 0 until args.size) {//设置sql参数
ps.setObject(i + 1, args[i])
}
var resultSet = ps.executeQuery()//获得查询结果集
var rsmd = resultSet.getMetaData()
var columns = rsmd.columnCount //获得结果集列数
if (resultSet.next()) {
var cust = Customer()
for (i in 0 until columns) {
var value = resultSet.getObject(i + 1) //获得查询的值
var columnsName = rsmd.getColumnName(i + 1) //获得值对应的列名，即字段名
//println("查询结果，${value}+${columnsName}")
val field = Customer::class.java.getDeclaredField(columnsName)
field.setAccessible(true)
field.set(cust, value)
}
println("已执行查询语句...")
return cust
}
// }).start()
} catch (e: Exception) {
e.printStackTrace()
}
return null
}
}
（4）添加按钮点击监听执行
package com.vocus.jdbctest
import android.os.Bundle
import androidx.appcompat.app.AppCompatActivity
import kotlinx.android.synthetic.main.activity_main.*
import kotlin.concurrent.thread class MainActivity : AppCompatActivity() { override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main) connBtn.setOnClickListener {
thread{
var cust = CustomerForQuery().query("select username from customer where id=?", "3")
println(cust)
}
}
} }
（5）优化关闭连接，引入泛型，实现针对不同表的通用查询等
------>DAO模式
（1）建立DAO抽象类(BaseDAO)
对于数据库的增删改查等基本操作封装在此类中
（2）针对具体的表提供接口(CustomerDAO)
用于说明具体的需求
interface CustomerDAO {
//查询用户名是否存在
fun logcheck(username:String,password:String):Boolean
}
（3）实现接口的类（CustomerDAOimpl）
用于需求的实现
class CustomerDAOImpl :BaseDAO(),CustomerDAO {
override fun logcheck(username: String, password: String): Boolean {
var cust:Customer?=null
try{
cust=query("select password from customer where username=?",username)
}catch (e:Exception){
println("用户名不存在...")
return false
}
if(cust!!.password==password){
return true
}
else{
println("账号和用户名不匹配...")
return false
}
}
}
class MainActivity : AppCompatActivity() {
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
connBtn.setOnClickListener {
thread{
//var cust = CustomerForQuery().query("select password from customer where username=?", "张三")
//println(cust)
var exist=CustomerDAOImpl().logcheck("张三","123456")
println("exist?:${exist}")
}
}
}
}
接2.17更新
现在开发后台基本上很少用kotlin，最近尝试做后台，所以用到的jdbc都采用java
实现数据库表的通用查询，以及数据库表通用查询下的返回多条数据，使用到泛型，测试代码如下
->数据库表查询通用方法
public <T> T queryForTable(Class<T> clazz,String sql,Object ...args){
Connection conn=null;
PreparedStatement statement=null;
ResultSet rs=null;
try{
conn=JdbcUtil.connection();
statement=conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
statement.setObject(i+1,args[0]);
}
rs=statement.executeQuery();
//获得结果集原数据
ResultSetMetaData rsmd=rs.getMetaData();
int columnCount=rsmd.getColumnCount();
if(rs.next()){
T t=clazz.newInstance();
for(int i=0;i<columnCount;i++){
Object columnValue=rs.getObject(i+1);
String columnName=rsmd.getColumnName(i+1);
//把查询到的每一行数据，映射到java类
Field field=clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
}catch (Exception e){
e.printStackTrace();
}finally {
new JdbcUtil().closeResource(conn,statement,rs);
}
return null;
}
->返回多条数据
public <T> ArrayList<T> query(Class<T> clazz,String sql,Object ...args) {
ResultSet rs = null;
PreparedStatement statement = null;
Connection conn = new JdbcUtil().connection();
ArrayList<T> customersList=new ArrayList<T>();//对象列表
try {
//String sql = "select * from customer limit 0,10;";
statement = conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
statement.setObject(i+1,args[i]);
}
rs = statement.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
int columnCount=rsmd.getColumnCount();
for (int j=0;rs.next();j++) {
T t=clazz.newInstance();
for(int i=0;i<columnCount;i++){
String columnName=rsmd.getColumnName(i+1);
Object columnValue=rs.getObject(i+1);
Field field=clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t,columnValue);
}
customersList.add(t);
}
} catch (
SQLException | NoSuchFieldException | IllegalAccessException | InstantiationException e) {
e.printStackTrace();
} finally {
new JdbcUtil().closeResource(conn, statement, rs);
}
return customersList;
}