引入模块
dependencies:
flutter:
sdk: flutter
mysql1: ^0.20.0
mysql helper 的代码
import 'dart:async';
import 'package:mysql1/mysql1.dart';
class MySqlHelper {
static const _host = 'localhost';
static const _port = 3333;
static const _user = 'user';
static const _db = 'table';
static const _password = 'pass';
MySqlConnection? _connection;
MySqlHelper() {
_init();
}
Future<void> _init() async {
try {
_connection = await MySqlConnection.connect(ConnectionSettings(
host: _host,
port: _port,
user: _user,
db: _db,
password: _password,
));
} catch (e) {
// Handle the exception appropriately, e.g., log the error or rethrow.
throw Exception('Failed to connect to the database: $e');
}
}
Future<int?> create(String tableName, Map<String, dynamic> data) async {
if (_connection == null) {
throw Exception('Database connection is not initialized');
}
var columns = data.keys.join(', ');
var placeholders = List.generate(data.length, (index) => '?').join(', ');
var values = data.values.toList();
var query = 'INSERT INTO $tableName ($columns) VALUES ($placeholders)';
var result = await _connection!.query(query, values);
return result.insertId;
}
Future<List<Map<String, dynamic>>> read(String tableName) async {
if (_connection == null) {
throw Exception('Database connection is not initialized');
}
var query = 'SELECT * FROM $tableName';
var results = await _connection!.query(query);
return results.map((row) => Map.fromEntries(row.asMap().entries.map((e) => MapEntry(e.key.toString(), e.value)))).toList();
}
Future<void> update(String tableName, Map<String, dynamic> data, String whereColumn, dynamic whereValue) async {
if (_connection == null) {
throw Exception('Database connection is not initialized');
}
var sets = data.entries.map((e) => '${e.key} = ?').join(', ');
var values = data.values.toList()..add(whereValue);
var query = 'UPDATE $tableName SET $sets WHERE $whereColumn = ?';
await _connection!.query(query, values);
}
Future<void> delete(String tableName, String whereColumn, dynamic whereValue) async {
if (_connection == null) {
throw Exception('Database connection is not initialized');
}
var query = 'DELETE FROM $tableName WHERE $whereColumn = ?';
await _connection!.query(query, [whereValue]);
}
Future<void> close() async {
if (_connection != null) {
await _connection!.close();
_connection = null;
}
}
}
// 使用示例
void main() async {
final helper = MySqlHelper();
// 等待数据库连接初始化完成
await Future.delayed(Duration(seconds: 2)); // 仍然使用延迟作为示例,实际中应该监听连接状态
try {
// 插入数据
int? insertId = await helper.create('your_table_name', {
'column1': 'value1',
'column2': 'value2',
});
print('Inserted row with ID: $insertId');
// 读取数据
List<Map<String, dynamic>> rows = await helper.read('your_table_name');
for (var row in rows) {
print(row);
}
// 更新数据
await helper.update('your_table_name', {
'column1': 'updated_value1',
}, 'id', insertId);
// 删除数据
await helper.delete('your_table_name', 'id', insertId);
} catch (e) {
print('An error occurred: $e');
} finally {
// 关闭数据库连接
await helper.close();
}
}
// 使用示例
void main() async {
final helper = MySqlHelper();
// 等待数据库连接初始化完成
await Future.delayed(Duration(seconds: 2)); // 仍然使用延迟作为示例,实际中应该监听连接状态
try {
// 插入数据
int? insertId = await helper.create('your_table_name', {
'column1': 'value1',
'column2': 'value2',
});
print('Inserted row with ID: $insertId');
// 读取数据
List<Map<String, dynamic>> rows = await helper.read('your_table_name');
for (var row in rows) {
print(row);
}
// 更新数据
await helper.update('your_table_name', {
'column1': 'updated_value1',
}, 'id', insertId);
// 删除数据
await helper.delete('your_table_name', 'id', insertId);
} catch (e) {
print('An error occurred: $e');
} finally {
// 关闭数据库连接
await helper.close();
}
}