Android connects to MySQL database and explains the operation examples of adding deleting modifying and searching
- 2021-12-05 07:08:55
- OfStack
1. Android connects to MySQL database
public class DBOpenHelper {
private static String driver = "com.mysql.jdbc.Driver";//MySQL Drive
private static String url = "jdbc:mysql://IP:3306/ Database ";//MYSQL Database connection Url
private static String user = "root";// User name
private static String password = "root";// Password
/**
* Connect to a database
* */
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(driver);// Get MYSQL Drive
conn = (Connection) DriverManager.getConnection(url, user, password);// Get a connection
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* Close the database
* */
public static void closeAll(Connection conn, PreparedStatement ps){
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Close the database
* */
public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs){
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2. Add, delete and check
public class DBService {
private Connection conn=null; // Open a database object
private PreparedStatement ps=null;// Operational integration sql Object of the statement
private ResultSet rs=null;// Collection of query results
//DBService Object
public static DBService dbService=null;
/**
* Construction method Privatization
* */
private DBService(){
}
/**
* Get MySQL Database singleton class object
* */
public static DBService getDbService(){
if(dbService==null){
dbService=new DBService();
}
return dbService;
}
/**
* Get the patient information to send SMS Check
* */
public List<User> getUserData(){
// Result storage collection
List<User> list=new ArrayList<User>();
//MySQL Statement
String sql="select * from user";
// Getting Linked Database Objects
conn= DBOpenHelper.getConn();
try {
if(conn!=null&&(!conn.isClosed())){
ps= (PreparedStatement) conn.prepareStatement(sql);
if(ps!=null){
rs= ps.executeQuery();
if(rs!=null){
while(rs.next()){
User u=new User();
u.setId(rs.getString("id"));
u.setName(rs.getString("name"));
u.setPhone(rs.getString("phone"));
u.setContent(rs.getString("content"));
u.setState(rs.getString("state"));
list.add(u);
}
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
DBOpenHelper.closeAll(conn,ps,rs);// Close related operations
return list;
}
/**
* Modify the state of an object in the database Change
* */
public int updateUserData(String phone){
int result=-1;
if(!StringUtils.isEmpty(phone)){
// Getting Linked Database Objects
conn= DBOpenHelper.getConn();
//MySQL Statement
String sql="update user set state=? where phone=?";
try {
boolean closed=conn.isClosed();
if(conn!=null&&(!closed)){
ps= (PreparedStatement) conn.prepareStatement(sql);
ps.setString(1,"1");// No. 1 1 Parameters state 1 It must be with the above SQL Statement field order 1 To
ps.setString(2,phone);// No. 1 2 Parameters phone 1 It must be with the above SQL Statement field order 1 To
result=ps.executeUpdate();// Return 1 Successful execution
}
} catch (SQLException e) {
e.printStackTrace();
}
}
DBOpenHelper.closeAll(conn,ps);// Close related operations
return result;
}
/**
* Insert data into the database in batches Increase
* */
public int insertUserData(List<User> list){
int result=-1;
if((list!=null)&&(list.size()>0)){
// Getting Linked Database Objects
conn= DBOpenHelper.getConn();
//MySQL Statement
String sql="INSERT INTO user (name,phone,content,state) VALUES (?,?,?,?)";
try {
boolean closed=conn.isClosed();
if((conn!=null)&&(!closed)){
for(User user:list){
ps= (PreparedStatement) conn.prepareStatement(sql);
String name=user.getName();
String phone=user.getPhone();
String content=user.getContent();
String state=user.getState();
ps.setString(1,name);// No. 1 1 Parameters name Same rules as above
ps.setString(2,phone);// No. 1 2 Parameters phone Same rules as above
ps.setString(3,content);// No. 1 3 Parameters content Same rules as above
ps.setString(4,state);// No. 1 4 Parameters state Same rules as above
result=ps.executeUpdate();// Return 1 Successful execution
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
DBOpenHelper.closeAll(conn,ps);// Close related operations
return result;
}
/**
* Delete data Delete
* */
public int delUserData(String phone){
int result=-1;
if((!StringUtils.isEmpty(phone))&&(PhoneNumberUtils.isMobileNumber(phone))){
// Getting Linked Database Objects
conn= DBOpenHelper.getConn();
//MySQL Statement
String sql="delete from user where phone=?";
try {
boolean closed=conn.isClosed();
if((conn!=null)&&(!closed)){
ps= (PreparedStatement) conn.prepareStatement(sql);
ps.setString(1, phone);
result=ps.executeUpdate();// Return 1 Successful execution
}
} catch (SQLException e) {
e.printStackTrace();
}
}
DBOpenHelper.closeAll(conn,ps);// Close related operations
return result;
}
}