java实现sql生成器 可生成ddl dml dql
时间: 2023-08-22 10:05:06 浏览: 76
Java中可以使用数据库连接池来连接数据库进行操作,然后通过拼接SQL语句的方式实现SQL生成器。下面是一个简单的示例:
1. 定义一个数据库连接池的类,用于获取数据库连接:
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DBUtil {
private static DataSource ds = new ComboPooledDataSource();
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
}
```
2. 定义一个SQL生成器类,用于生成DDL、DML和DQL语句:
```java
import java.util.ArrayList;
import java.util.List;
public class SQLBuilder {
private String tableName;
private List<String> columns = new ArrayList<>();
private List<String> values = new ArrayList<>();
private List<String> conditions = new ArrayList<>();
public SQLBuilder(String tableName) {
this.tableName = tableName;
}
public SQLBuilder addColumn(String column) {
columns.add(column);
return this;
}
public SQLBuilder addValue(String value) {
values.add(value);
return this;
}
public SQLBuilder addCondition(String condition) {
conditions.add(condition);
return this;
}
public String createDDL() {
StringBuilder sb = new StringBuilder();
sb.append("CREATE TABLE ").append(tableName).append("(");
for (String column : columns) {
sb.append(column).append(",");
}
sb.deleteCharAt(sb.length() - 1);
sb.append(")");
return sb.toString();
}
public String createDML() {
StringBuilder sb = new StringBuilder();
sb.append("INSERT INTO ").append(tableName).append(" (");
for (String column : columns) {
sb.append(column).append(",");
}
sb.deleteCharAt(sb.length() - 1);
sb.append(") VALUES (");
for (String value : values) {
sb.append(value).append(",");
}
sb.deleteCharAt(sb.length() - 1);
sb.append(")");
return sb.toString();
}
public String createDQL() {
StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM ").append(tableName);
if (!conditions.isEmpty()) {
sb.append(" WHERE ");
for (String condition : conditions) {
sb.append(condition).append(" AND ");
}
sb.delete(sb.length() - 5, sb.length());
}
return sb.toString();
}
}
```
3. 使用SQL生成器生成SQL语句:
```java
public class Main {
public static void main(String[] args) {
SQLBuilder builder = new SQLBuilder("user");
String ddl = builder.addColumn("id INT PRIMARY KEY AUTO_INCREMENT")
.addColumn("name VARCHAR(50)")
.addColumn("age INT")
.createDDL();
System.out.println(ddl);
String dml = builder.addValue("1")
.addValue("'Tom'")
.addValue("20")
.createDML();
System.out.println(dml);
String dql = builder.addCondition("age > 18")
.createDQL();
System.out.println(dql);
}
}
```
上述代码生成的SQL语句分别为:
```sql
CREATE TABLE user(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),age INT)
INSERT INTO user (id,name,age) VALUES (1,'Tom',20)
SELECT * FROM user WHERE age > 18
```
当然,这只是一个简单的示例,实际应用中可能需要更复杂的SQL操作和更灵活的生成方式。