Как сделать сортировку в mysql?

Question

[arsden2]

Здраствуйте, для сортировки в mysql выполняю следующий код.

int i=0;
		String query = "SELECT `{field}` FROM `{table}` ORDER BY `{field}`";
		query = query.replace("{table}", txtTbl.getText());
		switch (jCBoxSort.getSelectedIndex()){
			case 0://типу
				query = query.replace("{field}", "id_vtype");
				break;

			case 1://по назві
				query = query.replace("{field}", "nazva");
				break;

			case 2://по жанру
				query = query.replace("{field}", "id_genre");
				break;

			case 3://по автору
				query = query.replace("{field}", "id_author");
				break;

			case 4://по описанию
				query = query.replace("{field}", "description");
				break;

			case 5://по id
				query = query.replace("{field}", "id");
				break;

			default:
				query = query.replace("{field}", "");

		}

		if (jRadioBtnZrost.isSelected()){//по зростанню
			query += " ASC";
		}
		else{//по спаданню
			query += " DESC";
		}
		///query(query);
		try {
			Statement statement = SQL.getConn().createStatement();
			statement.execute(query);
			Statement statement1 = SQL.getConn().createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			ResultSet resultBefore = statement1.executeQuery("SELECT id_author FROM video.video_list;");
			StringBuilder message = new StringBuilder();
			StringBuilder before = new StringBuilder();
			while(resultBefore.next()){
				before.append(resultBefore.getString(1) + " ");
			}
			while(resultSet.next()){
				message.append(resultSet.getString(1) + " ");
			}
            System.out.println("Now");
            System.out.println(message);
            System.out.println("Before");
            System.out.println(before);
			String[]data = message.toString().split(" ");
			String[]data1 = before.toString().split(" ");
			for(int d =0;d<data.length;d++) {
				statement.execute("update `video_list` set id_author=" + data[d] + " where id_author=" + data1[d]);
			}
		}catch (Exception e){
			e.printStackTrace();
		}

Но он не коректно выполняэться.Вот вывод в консоль
.

SELECT `id_vtype` FROM `video_list` ORDER BY `id_vtype` DESC
Now
2122 898 124 87 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 2 1 1 1 1 1 1 1 1 
Before
2 1 1 2 2 1 2 1 7 1 3 3 3 3 3 3 7 3 7 7 3 3 3 3 3 3 3 2 2 1 1

Comments

[Максим Федоров]

в чем некорректность? у Вас данные отсортированы

[arsden2]

Максим Федоров: не заноситься в бд почему-то.

[Rsa97]

arsden2: А как оно должно заноситься, если вы выбрали из базы `id_vtype`, а менять пытаетесь `id_author`?

[arsden2]

int i=0;
String query = "SELECT `{field}` FROM `{table}` ORDER BY `{field}`";
String request = "SELECT `{field}` FROM `{table}`;";
query = query.replace("{table}", txtTbl.getText());
request = request.replace("{table}", txtTbl.getText());
String fieldName;
switch (jCBoxSort.getSelectedIndex()){
case 0://типу
query = query.replace("{field}", "id_vtype");
request = request.replace("{field}", "id_vtype");
fieldName = "id_vtype";
System.out.println("Type");
break;

case 1://по назві
query = query.replace("{field}", "nazva");
request = request.replace("{field}", "nazva");
fieldName = "nazva";
System.out.println("Nazva");
break;

case 2://по жанру
query = query.replace("{field}", "id_genre");
request = request.replace("{field}", "id_genre");
fieldName = "id_genre";
System.out.println("genre");
break;

case 3://по автору
query = query.replace("{field}", "id_author");
request = request.replace("{field}", "id_genre");
fieldName = "id_author";
System.out.println("author");
break;

case 4://по описанию
query = query.replace("{field}", "description");
request = request.replace("{field}", "description");
fieldName = "description";
System.out.println("description");
break;

case 5://по id
query = query.replace("{field}", "id");
request = request.replace("{field}", "id");
fieldName = "id";
System.out.println("id");
break;

default:
query = query.replace("{field}", "");
request = request.replace("{field}","");
fieldName = "";
}

if (jRadioBtnZrost.isSelected()){//по зростанню
query += " ASC";
}
else{//по спаданню
query += " DESC";
}
///query(query);
System.out.println(query);
try {
System.out.println("Now");
System.out.println(printNormal(query));
System.out.println("Before");
System.out.println(printBefore(request));
String[]normal = printNormal(query).toString().split(" ");
String[] before = printBefore(request).toString().split(" ");
String tableName = txtTbl.getText();
for(int d =0;d

[arsden2]

вроде исправил.

[arsden2]

но все равно не добавляєт

Answer 1

[arsden2]

Решил сделать через временую таблицу - занесло.
Кому интересно, вот код:

int i=0;
		String query = "create table new as select * from {table} order by {field}";
        String request = "drop table {table};";
		String query1 = "create table {table} as select * from new order by {field}";
		String query2 = "drop table new;";
        query = query.replace("{table}", txtTbl.getText());
		query1 = query1.replace("{table}", txtTbl.getText());
		request = request.replace("{table}", txtTbl.getText());
		switch (jCBoxSort.getSelectedIndex()){
			case 0://типу
				query = query.replace("{field}", "id_vtype");
				query1 = query1.replace("{field}", "id_vtype");
                System.out.println("Type");
                break;

			case 1://по назві
				query = query.replace("{field}", "nazva");
				query1 = query1.replace("{field}", "nazva");
                System.out.println("Nazva");
                break;

			case 2://по жанру
				query = query.replace("{field}", "id_genre");
				query1 = query1.replace("{field}", "id_genre");
				System.out.println("genre");
                break;

			case 3://по автору
				query = query.replace("{field}", "id_author");
				query1 = query1.replace("{field}", "id_author");
				System.out.println("author");
                break;

			case 4://по описанию
				query = query.replace("{field}", "description");
				query1 = query1.replace("{field}", "description");
				System.out.println("description");
                break;

			case 5://по id
				query = query.replace("{field}", "id");
				query1 = query1.replace("{field}", "id");
                System.out.println("id");
                break;

			default:
				query = query.replace("{field}", "");
				query1 = query1.replace("{field}", "");
		}

		if (jRadioBtnZrost.isSelected()){//по зростанню
			query += " ASC;";
			query1 += " ASC;";
		}
		else{//по спаданню
			query += " DESC;";
			query1 += " DESC;";
		}
		System.out.println(query);
		System.out.println(request);
		System.out.println(query1);
		System.out.println(query2);
		query(query);
		query(request);
		query(query1);
		query(query2);